How to find last update in my table based on date and time

Question

I have table like below

|   time         | ename  | cid    | userid  |  day
-------------------------------------------------------
|  1455865003573 | fol    |  11228 | 107757  |20160218
|  1455865000083 | unfol  |  11228 | 107757  |20160218
|  1455874888381 | unfol  |  11229 | 107757  |20160219
|  1455874892944 | fol    |  11229 | 107757  |20160219

I want output like below based on last max(time) on each cid,userid,day.

|   time         | ename  | cid    | userid  |  day
-------------------------------------------------------
|  1455865003573 | fol    |  11228 | 107757  |20160218
|  1455874892944 | fol    |  11229 | 107757  |20160219

Please help me on this.

Thanks In Advance.

You need to decide which DBMS you are using instead of tagging all of them. — Sean Lange, Feb 19 '16 at 16:20
http://stackoverflow.com/questions/3491329/group-by-with-maxdate — Yevgeniy Shchemelev, Feb 19 '16 at 16:35
Hi all, I am using sql ,i have tried group By functionality. — Sai, Feb 19 '16 at 16:54

Firewizz · Answer 1 · 2016-02-19T16:26:21.300

0

Something like this?

SELECT * FROM tablename ORDER BY time DESC LIMIT 1

This would give you the row with the highest time.

or to get the same as what u said you wanted:

SELECT * FROM tablename WHERE ename='fol' ORDER BY time DESC

edited Feb 19 '16 at 16:26

answered Feb 19 '16 at 16:20

Firewizz

773
5
17

Hi Firewizz,i have so much of data(rows of data ) in the same date. so i should not use WHERE ename='fol') and desc. because ename may be fol/unfol depends on the Max(Time) – Sai Feb 19 '16 at 16:59

score 0 · Answer 2 · answered Feb 19 '16 at 19:56

0

If you are using sql server than this query is worked for you.

select * from (select ROW_NUMBER() over (partition by [cid],[userid],[day] 
order by [time] desc) RowNum,* from yourtablename)T where RowNum=1

answered Feb 19 '16 at 19:56

Shoaib Raza

61
5

How to find last update in my table based on date and time

2 Answers2