CODEWITHSUNDEEP
sqlgroup-by
Tutu
Tutu

Reputation: 3

Problem with the Group BY Clause

HI Guys

I have a table like the bellow one

SurveyID(uniqueidentifier) PKey,UserID(uniqueidentifier),Time(DateTime)

I can have duplicated for UserID and Time is the taken as the current time when we insert a record to the table.

I have to select the last inserted recrod for each user.

All my queries are failing. Any help on this?

Thanks Tutu

Upvotes: 0

Views: 79

Answers (4)

subhash
subhash

Reputation: 286

SELECT * FROM Users WHERE Time IN
(SELECT max(Time) FROM Users GROUP BY USerID)

Upvotes: 0

Mor Shemesh
Mor Shemesh

Reputation: 2897

SELECT u1.*
FROM Users u1
WHERE NOT EXISTS 
    (SELECT *
     FROM Users u2
     WHERE u1.ID = u2.ID AND u2.Date > u1.Date)

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

select * from
(
select *, row_number() over(partition by userid order by time desc) as sno from users
) as t
where sno=1

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97841

WITH rows AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY [Time] DESC) AS __ROW,
        SurveyID,
        PKey,
        UserID
    FROM yourTable
)
SELECT SurveyID, PKey, UserID
FROM rows
WHERE __ROW = 1;

This will work in Oracle and SQL Server. Not sure about MySQL or Postgres. It won't work in Access. The double-underscore __ROW is just a naming convention I find myself using sometimes.

Upvotes: 0

Related Questions