Andre
Andre

Reputation: 849

Select only the newer date using sqlserver

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

Answers (4)

SqlZim
SqlZim

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

Kannan Kandasamy
Kannan Kandasamy

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

IngoB
IngoB

Reputation: 2989

SELECT top 3 *
  FROM mytable 
 ORDER BY event_time DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

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

Related Questions