Reputation: 849
I have a table with only 3 columns that I am trying to select only the most recent/newer date:
account_id - event_time - sign
2251 2017-03-28 19:00:04 YES
2250 2017-03-28 15:45:11 NO
2250 2017-03-28 01:01:45 NO
2249 2017-03-24 21:00:03 YES
2248 2017-03-24 05:45:10 NO
2247 2017-03-19 21:00:05 YES
2246 2017-03-19 05:45:10 NO
2245 2017-03-22 21:15:05 YES
I am trying to get back these values because they are the newest values in the table:
2251 2017-03-28 19:00:04 YES
2250 2017-03-28 15:45:11 NO
2250 2017-03-28 01:01:45 NO
I tried:
SELECT account_id, max(event_time) as event_time, sign
FROM mytable
group by account_id,event_time, sign
order by event_time desc
But its bring all the records instead. Has anyone done anything like that? Thanks for looking!
Upvotes: 2
Views: 104
Reputation: 38053
Using top with ties
to return all records for the latest date:
select top 1 with ties
account_id
, event_time
, sign
from mytable
order by convert(date,event_time) desc
rextester demo: http://rextester.com/OBPT49409
returns:
+------------+---------------------+------+
| account_id | event_time | sign |
+------------+---------------------+------+
| 2251 | 2017-03-28 19:00:04 | YES |
| 2250 | 2017-03-28 15:45:11 | NO |
| 2250 | 2017-03-28 01:01:45 | NO |
+------------+---------------------+------+
For all records for the top 3 dates:
select
t.account_id
, t.event_time
, t.sign
from mytable t
inner join (
select distinct top 3
convert(date,event_time) as event_time
from mytable
order by event_time desc
) topthree
on convert(date,t.event_time) = topthree.event_time
returns:
+------------+---------------------+------+
| account_id | event_time | sign |
+------------+---------------------+------+
| 2251 | 2017-03-28 19:00:04 | YES |
| 2250 | 2017-03-28 15:45:11 | NO |
| 2250 | 2017-03-28 01:01:45 | NO |
| 2249 | 2017-03-24 21:00:03 | YES |
| 2248 | 2017-03-24 05:45:10 | NO |
| 2245 | 2017-03-22 21:15:05 | YES |
+------------+---------------------+------+
Upvotes: 1
Reputation: 13969
You can use subquery
select * from yourtable
where event_time >= (select convert(date, max(event_time)) from yourtable)
With this input
create table #yourlog(account_id int, event_time datetime, signin varchar(10))
insert into #yourlog (
account_id , event_time , signin)
values
( 2251 ,'2017-03-28 19:00:04','YES' )
,( 2250 ,'2017-03-28 15:45:11','NO ' )
,( 2250 ,'2017-03-28 01:01:45','NO ' )
,( 2249 ,'2017-03-24 21:00:03','YES' )
,( 2248 ,'2017-03-24 05:45:10','NO ' )
,( 2247 ,'2017-03-19 21:00:05','YES' )
,( 2246 ,'2017-03-19 05:45:10','NO ' )
,( 2245 ,'2017-03-22 21:15:05','YES' )
You get
account_id event_time signin
2251 2017-03-28 19:00:04.000 YES
2250 2017-03-28 15:45:11.000 NO
2250 2017-03-28 01:01:45.000 NO
Upvotes: 2
Reputation: 1270573
Use row_number()
:
select t.*
from (select t.*,
row_number() over (partition by account_id order by event_time desc) as seqnum
from mytable t
) t
where seqnum = 1
order by event_time desc;
Upvotes: 1