CODEWITHSUNDEEP
sql-server
Aks
Aks

Reputation: 50

how to get last inserted record by username in sql server

emp Table record Like:

empt Table

id  name    date    
1   ankit   18-12-2014 05:55:22.000
2   mehul   18-12-2014 04:44:22.000
3   vimal   18-12-2014 03:44:22.000
4   ankit   18-12-2014 06:00:22.000
5   mehul   18-12-2014 06:10:22.000

I want to output like:

4 ankit 18-12-2014 06:00:22.000
5 mehul 18-12-2014 06:10:22.000
3 vimal 18-12-2014 03:44:22.000

when insert new record like this :

vimal   18-12-2014 06:30:22.000

then output like:

4 ankit 18-12-2014 06:00:22.000
5 mehul 18-12-2014 06:10:22.000
6 vimal 18-12-2014 06:30:22.000

so how to possible this?

Upvotes: 1

Views: 164

Answers (3)

Dudi Konfino
Dudi Konfino

Reputation: 1136

N=NUMBER OF TABLE'S ROWS 

 DECLARE @X INT=(SELECT COUNT(*) FROM empt  )
    SELECT ID, 
           Name, 
           Date 
    FROM empt  
    ORDER BY ID OFFSET (@X-3) ROWS FETCH NEXT 3 ROWS ONLY;

Upvotes: 0

Paolo
Paolo

Reputation: 2254

an alternative solution could be a join:

select K.*
from (
       select  X.Name, 
               max(X.Date) MaxDate
       from    emp X
     ) Y
     join emp K on K.name = Y.name and K.Date = Y.MaxDate

Upvotes: 0

GarethD
GarethD

Reputation: 69769

Use ROW_NUMBER(), since you want one record per username, this would be your partitioning field in the OVER clause (PARTITION BY Name), and then you want the latest record per username, so this would be your order in the OVER clause (ORDER BY Date DESC):

SELECT  ID, Name, Date
FROM    (   SELECT  ID, 
                    Name, 
                    Date,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY Name 
                                                    ORDER BY Date DESC)
            FROM    T
        ) AS T
WHERE   RowNumber = 1;

Upvotes: 2

Related Questions