CODEWITHSUNDEEP
sql
Umair
Umair

Reputation: 5481

Get latest sql rows based on latest date and per user

I have the following table:

RowId, UserId, Date
1, 1, 1/1/01
2, 1, 2/1/01
3, 2, 5/1/01
4, 1, 3/1/01
5, 2, 9/1/01

I want to get the latest records based on date and per UserId but as a part of the following query (due to a reason I cannot change this query as this is auto generated by a tool but I can write pass any thing starting with AND...):

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND ( 

// everything which needs to be done goes here . . .

)

I have tried similar query, but get an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

EDIT: Database is Sql Server 2008

Upvotes: 2

Views: 2736

Answers (4)

Andriy M
Andriy M

Reputation: 77677

You could use a NOT EXISTS condition:

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND NOT EXISTS (
  SELECT *
  FROM MyTable AS t
  WHERE t.UserId = MyTable.UserId
    AND t.Date > MyTable.Date
)
;

Note that if a user has more than one row with the same latest Date value, the query will return all such entries. If necessary, you can modify the subquery's condition slightly to make sure only one row is returned:

  WHERE t.UserId = MyTable.UserId
    AND (t.Date > MyTable.Date
      OR t.Date = MyTable.Date AND t.RowId > MyTable.RowId
    )

With the above condition, if two or more rows with the same Date exist for the same user, the one with the greater RowId value will be returned.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460138

Assuming that RowID is an identity column:

SELECT t1.RowId, t1.UserId, t1.Date
FROM MyTable t1
WHERE 1 = 1
AND t1.RowID IN ( 

    SELECT TOP 1 t2.RowID 
    FROM MyTable t2
    WHERE  t1.UserId = t2.UserId
    AND    t2.Date = (SELECT MAX(t3.Date) FROM MyTable t3
                      WHERE t2.UserID = t3.UserId)

)

Demo

Upvotes: 1

user359040
user359040

Reputation:

Try:

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND EXISTS
(SELECT 1
 FROM (SELECT UserId, MAX(Date) MaxDate
       FROM MyTable
       GROUP BY UserId) m
 WHERE m.UserId = MyTable.UserId and m.MaxDate = MyTable.Date)

SQLFiddle here.

Upvotes: 1

Kane
Kane

Reputation: 16802

Assuming you have the ability to modify anything within the AND clause you can do a query like this if you are using TSQL

SELECT RowId, UserId, [Date]
FROM @myTable
WHERE 1 = 1
AND ( 
    RowId IN (
            SELECT D.RowId
            FROM (
                SELECT DISTINCT MAX(RowId) AS RowId, UserId, MAX([Date]) AS [Date]
                FROM @myTable
                GROUP BY UserId
        ) AS D
    )
)

Upvotes: 1

Related Questions