3

I am trying to return just the first result of a query after it is sorted in alphabetical order:

SELECT MIN(DisplayName) FROM [User] GROUP BY DisplayName

I believed the above would do the trick. But it is still returing all results in alphabetical order. How do I get the desired results?

David Tunnell
  • 6,791
  • 20
  • 61
  • 115
  • 1
    Do you want only the very top record? Perhaps show an example of the data before you get it, and after in the format you'd like. – Elias Sep 17 '13 at 19:02
  • By way of explanation, you queried for the minimum value of `DisplayName` for _each_ (by virtue of the `GROUP BY` clause) distinct value of `DisplayName`. – HABO Sep 17 '13 at 19:38
  • I think you want one record per DisplayName initialize if so then pls see my answer .. http://stackoverflow.com/a/18858588/1230248 – Dhaval Sep 17 '13 at 19:45

8 Answers8

7

You don't need to GROUP BY:

SELECT MIN(DisplayName) 
FROM [User]

OR:

SELECT TOP 1 DisplayName
FROM [User]
ORDER BY DisplayName

GROUP BY will return each distinct value/combination of values for the fields listed.

Hart CO
  • 32,944
  • 5
  • 44
  • 59
  • Why is the GROUP BY not needed here? – jeppoo1 Jun 03 '20 at 11:35
  • @jeppoo1 `GROUP BY ` creates subsets of records in a table... imagine a table that listed cities in each of the 50 US states. If you grouped by the state you'd get 50 records, one for each state. So if all you wanted was the first state alphabetically, grouping by state wouldn't work, as it would give you 50 records instead of the 1 you wanted. If you wanted the first city of each state alphabetically, then grouping by state would make sense. – Hart CO Jun 03 '20 at 18:52
1

Only the first row? Simply remove the group by:

SELECT MIN(DisplayName) FROM [User]
dnoeth
  • 57,618
  • 3
  • 33
  • 50
1

To return the first record you can try the following:

Select TOP 1 MIN(DisplayName) FROM [User] ORDER BY DisplayName

This will sort the in Ascending order and it will select the first record

Jacques Bronkhorst
  • 1,625
  • 6
  • 34
  • 64
1

Here is a different method if you want to take the "result" and then join to antoher table.

Use Northwind
GO


Select derived1.CustomerID, o.*  from dbo.Orders o
join
(
SELECT top 1 CustomerID FROM [Customers] customers ORder by CustomerID
) as derived1
on derived1.CustomerID = o.CustomerID
granadaCoder
  • 23,729
  • 8
  • 95
  • 129
1

I think you want one record per name initialize if so then try following

here is working demo SQLFiddle

SELECT Left(DisplayName , 1) , MIN(DisplayName) FROM [User] GROUP BY Left(DisplayName , 1)

Dhaval
  • 2,763
  • 19
  • 38
0
select top 1 city, Len(city)
from station
group by Len(city), city
order by Len(city) asc

select top 1 city, Len(city)
from station
group by Len(city), city
order by Len(city) desc
Pang
  • 9,073
  • 146
  • 84
  • 117
asha
  • 1
0

If you are using ORACLE as your SQL IDE;

SELECT City, LENGTH(City)
FROM (SELECT City
      FROM Station
     ORDER BY LENGTH(City), City)
WHERE ROWNUM = 1;
SELECT City, LENGTH(City)
FROM (SELECT City
      FROM Station
     ORDER BY LENGTH(City) DESC, City)
WHERE ROWNUM = 1;
Bill Tür stands with Ukraine
  • 3,100
  • 27
  • 38
  • 45
Afolabi Kolawole
  • 11
  • 1
-1
select city, 
       length(city) 
from (select city,
             length(city) 
      from station 
      where length(city) in (select min(length(city)) from station) 
order by city asc) where rownum<=1;

select city,
       length(city) 
from station 
where length (city) in (select max(length(city)) from station);
Sim
  • 4,218
  • 4
  • 40
  • 70
ganesh bhagwat
  • 1
  • Please provide some explanation to the code snippet you've given (and format it as such to make it easier to read). It is very hard to grasp what you are trying to tell us when you don't elaborate a bit on what you've done here. – jBuchholz Jun 09 '20 at 07:39
  • As far as I can tell you are getting the first result of a list of cities whose length is minimal. This is not what OP wanted to achieve. – jBuchholz Jun 09 '20 at 07:40