查询以返回不同的行 [英] Query to return distinct rows

问题描述

大家好

Id  name     date             category   .....
1   Amritha  10/22/2013 8:30  ab         .....
2   Amritha  10/22/2013 8:32  ab         .....
3   raeah    10/22/2013 8.45  bc         .....
4   junu     10/22/2013 8:50  ac         .....
5   Junu     10/22/2013 8:55  ac         .....

这是我的带数据的表

从表中选择distinct * 返回所有行。我想要一个返回3行的查询

this is my table with data
select distinct * from table returns all the rows .I want a query which returns 3 rows

Id  name     date             category   .....
2   Amritha  10/22/2013 8:32  ab         .....
3   raeah    10/22/2013 8.45  bc         .....
5   Junu     10/22/2013 8:55  ac         .....

带有最新其他信息的不同名称。怎么做？



先谢谢



Amritha

distinct names with latest other information. How to do this?

Thanks in Advance

Amritha

推荐答案

你做不到。你不能说明显，然后也获得Amritha的ID 2。如何知道Amritha是否应该是ID 1或2。从你的输出看起来你几乎真的想要最大日期。

You can't do that. You can't say distinct and then also get ID 2 for Amritha. How will it know if it should be ID 1 or 2 for Amritha. From your output it almost looks like you actually want the max date.

SELECT Name, MAX(date) AS LatestDate
FROM table
GROUP BY Name

这将为每个名字提供1条记录，无论最大日期是什么。



如果你发生如果你需要该记录的ID，你可以使用上面的派生表并重新加入表格来获取它。

That will give 1 record per name and whatever the max date is.

If you happen to need the ID of that record as well you can use the above as a derived table and rejoin back to the table to get it.

SELECT t.Id, x.Name, x.LatestDate
FROM (
   SELECT Name, MAX(date) AS LatestDate
   FROM table
   GROUP BY Name
) x
INNER JOIN table t ON x.Name = t.Name and x.LatestDate = t.date

尝试此查询： -

Try this query:-

SELECT Min(Id),name, min(date)
FROM  Table_Name
GROUP BY name

Declare @tt table (Id int,name varchar(10),date varchar(20))
insert @tt
Select  1,'Amritha','10/22/2013 8:30'
union all Select 2,'Amritha','10/22/2013 8:32'
union all Select 3,'raeah','10/22/2013 8.45'
union all Select 4,'junu','10/22/2013 8:50'
union all Select 5,'Junu','10/22/2013 8:55'

Select Max(ID) AS ID,Name,Max(Date) AS Date From @tt
Group by Name

这篇关于查询以返回不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！