如何查询sql以获取每个用户的最新记录日期

I have a table that is a collection entries as to when a user was logged on.

我有一个表，它是用户登录时的集合项。

username, date,      value
--------------------------
brad,     1/2/2010,  1.1
fred,     1/3/2010,  1.0
bob,      8/4/2009,  1.5
brad,     2/2/2010,  1.2
fred,     12/2/2009, 1.3

etc..

How do I create a query that would give me the latest date for each user?

如何创建一个查询，为每个用户提供最新的日期?

Update: I forgot that I needed to have a value that goes along with the latest date.

更新:我忘记了我需要一个与最近日期一致的值。

17 个解决方案

#1

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

#2

SQL Server 2005 and up...

SQL Server 2005及以上…

select * from (
    select
        username,
        date,
        value,
        row_number() over(partition by username order by date desc) as rn
    from
        yourtable
) t
where t.rn = 1

#3

To get the whole row containing the max date for the user:

获取包含用户的最大日期的整行:

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

#4

I see most of the developers use an inline query without considering its impact on huge data.

我看到大多数开发人员使用内联查询而不考虑它对庞大数据的影响。

Simply, you can achieve this by:

简单地说，你可以通过:

SELECT a.username, a.date, a.value
FROM myTable a
LEFT OUTER JOIN myTable b
ON a.username = b.username 
AND a.date < b.date
WHERE b.username IS NULL
ORDER BY a.datedesc;

#5

SELECT *     
FROM MyTable T1    
WHERE date = (
   SELECT max(date)
   FROM MyTable T2
   WHERE T1.username=T2.username
)

#6

This one should give you the correct result for your edited question.

这个应该给你正确的结果，你编辑的问题。

The sub-query makes sure to find only rows of the latest date, and the outer GROUP BY will take care of ties. When there are two entries for the same date for the same user, it will return the one with the highest value.

子查询确保只找到最近日期的行，而外部组将处理关系。当同一用户有两个相同日期的条目时，它将返回值最高的条目。

SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
       SELECT username, MAX( date ) date
       FROM your_table
       GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date

#7

For Oracle sorts the result set in descending order and takes the first record, so you will get the latest record:

对于Oracle，按照降序排序结果集并获取第一个记录，因此您将获得最新的记录:

select * from mytable
where rownum = 1
order by date desc

#8

SELECT Username, date, value
 from MyTable mt
 inner join (select username, max(date) date
              from MyTable
              group by username) sub
  on sub.username = mt.username
   and sub.date = mt.date

Would address the updated problem. It might not work so well on large tables, even with good indexing.

将解决更新的问题。即使有很好的索引，它在大型表上也可能不太好用。

#9

SELECT *
FROM ReportStatus c
inner join ( SELECT 
  MAX(Date) AS MaxDate
  FROM ReportStatus ) m
on  c.date = m.maxdate

#10

SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
            FROM MyTable
            GROUP BY username) as t2 ON  t2.username = t1.username AND t2.date = t1.date

#11

Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

从表1中选择*，其中lastest_date=(从表1中选择Max(latest_date)，其中user=yourUserName)

Inner Query will return the latest date for the current user, Outer query will pull all the data according to the inner query result.

内部查询将返回当前用户的最新日期，外部查询将根据内部查询结果提取所有数据。

#12

I used this way to take the last record for each user that I have on my table. It was a query to get last location for salesman as per recent time detected on PDA devices.

我用这种方法获取表上每个用户的最后记录。这是一个查询，根据最近在PDA设备上检测到的时间获取销售员的最后位置。

CREATE FUNCTION dbo.UsersLocation()
RETURNS TABLE
AS
RETURN
Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate'
From USERGPS GS
where year(GS.UTCDateTime) = YEAR(GETDATE()) 
Group By GS.UserID
GO
select  gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude
        from USERGPS gs
        inner join USER s on gs.SalesManNo = s.SalesmanNo 
        inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate 
        order by LastDate desc

#13

SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)

#14

My small compilation

我的小编译

• self join better than nested select
• 自连接优于嵌套选择
• but group by doesn't give you primary key which is preferable for join
• 但是group by并没有给您主键，这对于join是比较好的
• this key can be given by partition by in conjunction with first_value (docs)
• 这个键可以通过与first_value(文档)结合的分区来给出

So, here is a query:

这里有一个查询:

select
 t.*
from 
 Table t inner join (
  select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID
  from Table
  where FilterColumn = 'value'
 ) j on t.ID = j.ID

Pros:

优点:

• Filter data with where statement using any column
• 使用任何列使用where语句过滤数据
• select any columns from filtered rows
• 从过滤过的行中选择任何列

Cons:

缺点:

• Need MS SQL Server starting with 2012.
• 从2012年开始需要MS SQL Server。

#15

You would use aggregate function MAX and GROUP BY

你可以使用聚合函数MAX和GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value

#16

SELECT DISTINCT Username, Dates,value 
FROM TableName
WHERE  Dates IN (SELECT  MAX(Dates) FROM TableName GROUP BY Username)


Username    Dates       value
bob         2010-02-02  1.2       
brad        2010-01-02  1.1       
fred        2010-01-03  1.0       

#17

This should also work in order to get all the latest entries for users.

这也应该能够为用户获取所有最新的条目。

SELECT username, MAX(date) as Date, value
FROM MyTable
GROUP BY username, value