'SQL Server top function return smallest length string

Given this problem

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Input Format

The STATION table is described as follows:

SQL Table schema

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

This is my solution.

SELECT TOP 1 * FROM CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY), CITY ASC;
SELECT TOP 1 * FROM CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC;

From how I am reading this W3 schools TOP

SELECT TOP 3 * FROM Customers;

My solution should work.

However, I am receiving this error.

Msg 156, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 6 
Incorrect syntax near the keyword 'FROM'. 
Msg 156, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 7 
Incorrect syntax near the keyword 'FROM'.
sqlsql-serverhtmljquerycss


Solution 1:[1]

The error caused you len function might put in the select clause instead of from clause

You can try to use ROW_NUMBER with window function to create row number order by CITY ASC (your logic) then get rn = 1 row.

select CITY,LEN(CITY) from (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY LEN(CITY) ORDER BY CITY ASC) rn 
    FROM STATION 
) t1
where t1.rn =1

sqlfiddle

Solution 2:[2]

Try:

SELECT CITY, length FROM
(
    select TOP 1 CITY, LEN(CITY) as length
    from STATION
    order by LEN(CITY)
) [Min]
UNION ALL
SELECT CITY, length FROM
(
    select TOP 1 CITY, LEN(CITY) as length
    from STATION
    order by LEN(CITY) desc
) [Max]

Solution 3:[3]

select top 1 CITY, len(CITY) from STATION order by len(CITY) asc,CITY;

enter image description here

Solution 4:[4]

$(".tooltipLink")
    .hover(
        function () {
            var title = $(this).attr("data-tooltip");
            $(this).data("tipText", title);
            if (title == "") {
            } else {
                $('<p class="tooltip"></p>').fadeIn(200).text(title).appendTo("body");
            }
        },
        function () {
            $(this).attr("data-tooltip", $(this).data("tipText"));
            $(".tooltip").fadeOut(200);
        }
    )
    .mousemove(function (e) {
        var mousex = e.pageX;
        var mousey = e.pageY - $(".tooltip:visible").outerHeight(true);
        $(".tooltip").css({
            top: mousey,
            left: mousex,
        });
    });
.tooltip {
    transform: translate(-50%, -200%);
    transform: translateX(-50%);
    display: none;
    position: absolute;
    color: #f0b015;
    background-color: #000;
    border: none;
    border-radius: 4px;
    padding: 15px 10px;
    z-index: 10;
    display: block;
    width: 100%;
    max-width: 200px;
    top: 0;
    left: 50%;
    text-align: center;
}
.tooltip:after {
    content: "";
    display: block;
    position: absolute;
    border-color: #000000 rgba(0, 0, 0, 0);
    border-style: solid;
    border-width: 15px 15px 0;
    bottom: -13px;
    left: 50%;
    transform: translate(-50%, 0);
    width: 0;
}
/* ----------------------- Display ------------------------ */
* {
    box-sizing: border-box;
}
body {
    font-family: "Quicksand", sans-serif;
    text-align: center;
    padding: 20px 50px 0;
    background: #222;
    color: #eee;
    height: 100vh;
}
.wrapper {
    margin: 100px auto;
    display: flex;
    flex-flow: row wrap;
    justify-content: space-around;
    align-items: flex-start;
    /*     overflow: auto; */
}
.wrapper > * {
    margin-bottom: 100px;
    flex-shrink: 0;
    max-width: 300px;
}
img {
    max-width: 300px;
}
hr {
    max-width: 100px;
    margin: 30px auto 0;
    border-bottom: 1px solid #444;
}
h1 {
    color: #f0b015;
    font-size: 2em;
}
a {
    color: #eee;
}
input {
    text-align: left;
}
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<h1>Moving Tooltips on anything.</h1> 
<h2>Very light on the JS</h2>
<hr>

<div class="wrapper">
      
    <a href="" class="tooltipLink" data-tooltip="space Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show is">I'm a hyperlink</a>
 
    <p class="tooltipLink" data-tooltip="space Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show is"> <strong>A Paragraph</strong><br><br>space Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show is, in this moment, powerful; the empathy she extends to her guests feels real and deep; the conversations</p>

    <input type="text" class="tooltipLink" data-tooltip="space Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show is" value="text input"></input>

<button type="text" class="tooltipLink" data-tooltip="I am a Button!">Button</button>

    <img class="tooltipLink" data-tooltip="space Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show isspace Anna Sale creates on the WNYC podcast Death, Sex, and Money. Her show is" src="https://s3-us-west-2.amazonaws.com/s.cdpn.io/30256/karate.jpg">
     
</div>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Dimith
Solution 3 Willie Cheng
Solution 4 Nikhil Parmar

Related Questions