我正在尝试对查询结果进行字母顺序排序,并仅返回第一个结果:
我认为上述方法可以解决问题。但是它仍然按字母顺序返回所有结果。我该如何获得期望的结果?
SELECT MIN(DisplayName) FROM [User] GROUP BY DisplayName
我认为上述方法可以解决问题。但是它仍然按字母顺序返回所有结果。我该如何获得期望的结果?
SELECT MIN(DisplayName) FROM [User] GROUP BY DisplayName
无需使用 GROUP BY
:
SELECT MIN(DisplayName)
FROM [User]
或:
SELECT TOP 1 DisplayName
FROM [User]
ORDER BY DisplayName
GROUP BY
语句将按照指定的字段对数据进行分组,并返回每个不同的值或值组合。
GROUP BY
在表中创建记录子集... 想象一下一个列出了美国50个州中每个城市的表格。如果按州分组,您将获得50条记录,每个州一条。因此,如果您只想要字母表顺序的第一个州,按州分组就行不通,因为它会给您50条记录而不是您想要的1条。如果您想要每个州字母表顺序的第一个城市,则按州分组是有意义的。 - Hart CO只需要第一行吗?那就直接删掉 group by:
SELECT MIN(DisplayName) FROM [User]
要返回第一条记录,您可以尝试以下操作:
Select TOP 1 MIN(DisplayName) FROM [User] ORDER BY DisplayName
这将按升序排序并选择第一条记录。
如果您想将“结果”与另一个表连接起来,可以尝试以下不同的方法。
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
Select city, length(city) from station
where length(city) = (Select max(length(city))from station)
order by city limit 1;
Select city, length(city) from station
where length(city) = (Select min(length(city)) from station)
order by city limit 1;
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;
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
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);
DisplayName
值(通过GROUP BY
子句)的最小DisplayName
值。 - HABO