Discussion:
Help with query
(too old to reply)
q***@gmail.com
2014-10-25 10:35:52 UTC
Permalink
In MS SQL Server 2012:

How do one get only the last inserted row (stime) for each user?

user |sdate |stime
--------------------
user1|20141024|134400
user1|20141024|163000
user1|20141024|162800
user2|20141024|070700
user2|20141024|165500
user3|20141024|074100
user3|20141024|151400
user3|20141024|121100

Expected result:
user |sdate |stime
--------------------
user1|20141024|163000
user2|20141024|165500
user3|20141024|151400

//UE
Erland Sommarskog
2014-10-25 10:47:45 UTC
Permalink
Post by q***@gmail.com
How do one get only the last inserted row (stime) for each user?
user |sdate |stime
--------------------
user1|20141024|134400
user1|20141024|163000
user1|20141024|162800
user2|20141024|070700
user2|20141024|165500
user3|20141024|074100
user3|20141024|151400
user3|20141024|121100
user |sdate |stime
--------------------
user1|20141024|163000
user2|20141024|165500
user3|20141024|151400
WITH numbering AS (
SELECT user, sdate, stime,
row_number() OVER (PARTITION BY user
ORDER BY sdate DESC, stime DESC) AS rowno
FROM tbl
)
SELECT user, sdate, stime
FROM numbering
WHERE rowno = 1
--
Erland Sommarskog, Stockholm, ***@sommarskog.se
q***@gmail.com
2014-10-25 12:30:25 UTC
Permalink
Post by Erland Sommarskog
Post by q***@gmail.com
How do one get only the last inserted row (stime) for each user?
user |sdate |stime
--------------------
user1|20141024|134400
user1|20141024|163000
user1|20141024|162800
user2|20141024|070700
user2|20141024|165500
user3|20141024|074100
user3|20141024|151400
user3|20141024|121100
user |sdate |stime
--------------------
user1|20141024|163000
user2|20141024|165500
user3|20141024|151400
WITH numbering AS (
SELECT user, sdate, stime,
row_number() OVER (PARTITION BY user
ORDER BY sdate DESC, stime DESC) AS rowno
FROM tbl
)
SELECT user, sdate, stime
FROM numbering
WHERE rowno = 1
--
Thank you very much for your quick response.

//UE

Loading...