Order By Minimum distance from getdate

Question

I have this table

Id_User Id_Subscription Date_Expiration
1       1               2013-01-21
1       2               2013-01-28    
2       1               2013-01-15
2       2               2013-01-30
2       3               2013-01-31

I want to order users with min distance from getdate (regardless of Id_Subscription) order by distance desc. Example:

Position Id_User   Distance in day
1        2         7
2        1         2

I have tried to do that

SELECT  ROW_NUMBER() OVER(ORDER BY COALESCE(MIN(us.Date_Expiration), 
CAST('2015-01-29 16:30:23.000' AS DATE)) DESC, us.id_user) AS Row, us.id_user
FROM User_Subscription us
GROUP BY a.Id_Anagrafica

but it's not correct.

i guess the last two row of table are duplicate? or they are required? — Priyank Doshi, Jan 23 '13 at 10:53
I think userId (2) max distance should be 8 and I am not sure how you get userId(1) max distance 2. I think it should be 5? — Kaf, Jan 23 '13 at 11:04
Sorry i've made and error. I want Min distance from Date_Expiration order by distance desc — Luigi Saggese, Jan 23 '13 at 11:12

Tim Schmelter · Accepted Answer · 2013-01-23T11:13:28.187

3

You can use ABS with Datediff, for example:

SELECT Row_number() 
         OVER( 
           ORDER BY Abs(Datediff(dd, Getdate(), Date_Expiration)) ASC, 
         us.id_user) AS Row, 
       us.id_user 
FROM   user_subscription us

To complete your question. I assume you want the maximum distance in days for each user. So you can use PARTION BY in the OVER clause, for example (not tested, with SQL-Server >= 2005):

WITH cte 
     AS (SELECT Position = Row_number() 
                             OVER( 
                               partition BY id_user 
                               ORDER BY Abs(Datediff(dd, Getdate(), 
                             date_expiration ) 
                             ) 
                             DESC), 
                id_user, 
                [Distance in day] = Abs(Datediff(dd, Getdate(), date_expiration) 
                                    ) 
         FROM   user_subscription) 
SELECT position, 
       id_user, 
       [distance in day] AS [Max Distance in day] 
FROM   cte 
WHERE  position = 1

DEMO

edited Jan 23 '13 at 11:13

answered Jan 23 '13 at 10:45

Tim Schmelter

450,073
74
686
939

1

WHERE position =1 what mean? – Luigi Saggese Jan 23 '13 at 11:07
@LuigiSaggese: That's the record for each user with the maximum distance from `GetDate` since `ORDER BY Abs(Datediff(dd, Getdate(), date_expiration)) DESC`. – Tim Schmelter Jan 23 '13 at 11:08
Sorry i've made and error. I want Min distance from Date_Expiration order by distance desc – Luigi Saggese Jan 23 '13 at 11:11
1

Here is [sql fiddle](http://sqlfiddle.com/#!3/66a7f/2). I think it is not working. – Kaf Jan 23 '13 at 11:11
1

@Kaf: Thanks, i've missed the `FROM` clause in the select in the `CTE` ;) Edited my answer and added the sql-fiddle. – Tim Schmelter Jan 23 '13 at 11:14
@LuigiSaggese: Added Kaf's Sql-Fiddle and changed `Order By ... DESC` to `... ASC`. Now i get the desired result, although the name `"Max Distance in day"` is misleading now. – Tim Schmelter Jan 23 '13 at 11:16
@TimSchmelter i have change column name. :-) You must use order by [distance in day] desc. But if i'd like to have a ranking position in result set? – Luigi Saggese Jan 23 '13 at 11:20
@LuigiSaggese: You don't need to use the `WHERE` clause to constrain the result set. – Tim Schmelter Jan 23 '13 at 11:22

Kaf · Answer 2 · 2013-01-23T11:17:59.980

0

Try this query. Here is Sql-fiddle example

select row_number() over(order by numberofdays desc) position, 
           Id_User, numberofdays 
from (
     select Id_User, abs(datediff(day, getdate(), Date_Expiration )) numberofdays,
            rank() over (partition by Id_User order by abs(datediff(day, getdate(),  Date_Expiration )) ) rnk
     from user_subscription
    ) A
where rnk = 1


--Results
POSITION    ID_USER NUMBEROFDAYS
1           2        7
2           1        2

edited Jan 23 '13 at 11:17

answered Jan 23 '13 at 10:47

Kaf

33,101
7
58
78

Sorry i've made and error. I want Min distance from Date_Expiration order by distance desc. Check your answer. – Luigi Saggese Jan 23 '13 at 11:13
1

@LuigiSaggese: Okay sorted. it gives what you need now. – Kaf Jan 23 '13 at 11:18

Priyank Doshi · Answer 3 · 2013-01-23T11:11:19.347

0

With following table structure

create table user1(id_user number,id_sub number,date_exp date);

Try this :

select row_number()   OVER (ORDER BY user1.id_user) position,id_user,MAX(date_exp)-MIN(date_exp) Max_Distance_in_day from user1 group by id_user;

with output:

| POSITION | ID_USER | MAX_DISTANCE_IN_DAY |
--------------------------------------------
|        1 |       1 |                   7 |
|        2 |       2 |                  16 |

Here's fiddle link

edited Jan 23 '13 at 11:11

answered Jan 23 '13 at 11:00

Priyank Doshi

12,895
18
59
82

2

Sorry i've made and error. I want Min distance from Date_Expiration order by distance desc – Luigi Saggese Jan 23 '13 at 11:13

Frederic · Answer 4 · 2013-01-23T12:04:34.387

0

select 
       ROW_NUMBER() over (order by min(abs(datediff(d,date_expiration,getdate()))) desc) [rank]
       ,id_user
       ,min(abs(datediff(d,date_expiration,getdate()))) as distance
from User_Subscription
group by id_user`

edited Jan 23 '13 at 12:04

answered Jan 23 '13 at 11:53

Frederic

1,018
6
11

Order By Minimum distance from getdate

4 Answers4