CODEWITHSUNDEEP
sqlamazon-redshift
user3490622
user3490622

Reputation: 1001

Find record closest to a given date for each group - SQL

I am new to sql. Suppose we have a table like this:

+-------+----------+-----------+
|userid | statusid |   date    |
+-------+----------+-----------+
| 1     |  1       | 2018-10-10| 
| 1     |  2       | 2018-10-12|
| 2     |  1       | 2018-09-25|
| 2     |  1       | 2018-10-01|
+-------+----------+-----------+

I need to get the stateid of each userid for a date as close to a given one as possible. Say my given date is 2018-10-01. How would I do that? I tried various groupby's and partition by, but nothing works. Could someone please help?

EDIT: my db is amazon redshift

Upvotes: 1

Views: 144

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

you can use row_number() window analytic function with ordered by absolute value of date difference.

( Note that row_number() doesn't work for MySQL 8-, so that function is not used but abs() function is. )

I don't know your DBMS

This solution is for Oracle :

with tab(userid, statusid, "date") as
(
 select 1,1,date'2018-10-10' from dual union all
 select 1,2,date'2018-10-12' from dual union all
 select 2,1,date'2018-09-25' from dual union all
 select 2,1,date'2018-10-02' from dual
)
select tt.userid, tt.statusid, tt."date"
  from
(
select t.userid, t.statusid , t."date",
       row_number() over (partition by t.userid 
                          order by abs("date" - date'2018-10-01')) as rn
  from tab t
) tt
where tt.rn = 1

Demo for Oracle

This solution is for SQL Server :

with tab([userid], [statusid], [date]) as
(
 select 1,1,'2018-10-10' union all
 select 1,2,'2018-10-12' union all
 select 2,1,'2018-09-25' union all
 select 2,1,'2018-10-02' 
)
select tt.[userid], tt.[statusid], tt.[date]
  from
(
select t.[userid], t.[statusid] , t.[date], 
       row_number() over (partition by t.[userid] 
                          order by abs(datediff(day,[date],'2018-10-01'))) as rn
  from tab t
) tt
where tt.rn = 1

Demo for SQL Server

The solution is for My SQL:

select tt.userid, tt.statusid, tt.date
  from
  (
   select t.userid, t.statusid , t.date,
          @rn := if(@iter = t.userid, @rn + 1, 1) as rn,
          @iter := t.userid, 
          abs(date - date'2018-10-01') as df
     from tab t
     join (select @iter := 0, @rn := 0) as q_iter
    order by t.userid, abs(date - date'2018-10-01') 
  ) tt
where tt.rn = 1

Demo for My SQL

This solution is for PostGRES :

with tab(userid, statusid, date) as
(
 select 1,1,'2018-10-10' union all
 select 1,2,'2018-10-12' union all
 select 2,1,'2018-09-25' union all
 select 2,1,'2018-10-02' 
)
select tt.userid, tt.statusid, tt.date
  from
(
select t.userid, t.statusid , t.date, 
       row_number() over (partition by t.userid
                          order by abs(date::date-'2018-10-01'::date)) as rn
  from tab t
) tt
where tt.rn = 1

Demo for PostGRESql

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Usually for this type of problem, you want the date on or before the given date.

If so:

select t.*
from t
where t.date = (select max(t2.date)
                from t t2
                where t2.userid = t.userid and t2.date <= '2018-10-01'
               );

Upvotes: 0

Related Questions