CODEWITHSUNDEEP
sqlpostgresql
Tom
Tom

Reputation: 797

PostgreSQL. Find max date range in records

I have this records:

id | user_id |    date   
 1 |    1    | 2015-01-01
 2 |    1    | 2015-01-05
 3 |    2    | 2014-01-01
 4 |    2    | 2014-01-30
 5 |    3    | 2014-01-25
 6 |    3    | 2014-02-28

I want to select date ranges to each user, sorted by date range length.

So, in the results I want to get this:

 user_id |  min_date   |  max_date
    2    |  2014-01-01 | 2014-01-30
    3    |  2014-01-25 | 2014-02-28
    1    |  2015-01-01 | 2015-01-05

Is it possible to do with sql query?

http://sqlfiddle.com/#!15/f958b

Upvotes: 4

Views: 1147

Answers (2)

realnumber3012
realnumber3012

Reputation: 1062

SELECT * FROM
(
SELECT user_id, MIN(date) AS min_date, 
MAX(date) AS max_date FROM test_table 
GROUP BY user_id
) t
ORDER BY max_date- min_date DESC

Upvotes: 1

Philip Couling
Philip Couling

Reputation: 14893

I must be missing something obveous is this not just a simple group by?

   SELECT user_id, 
          min(date), 
          max(date) 
     FROM test_table
 GROUP BY user_id 
 ORDER BY max(date) - min(date) DESC, 
          user_id;

Upvotes: 2

Related Questions