PostgreSQL Find the maximum date range in records

Question

I have entries:

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 for each user, sorted by the length of the date range.

So, in the results I want to get the following:

  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?

+4

Tom Aug 10 '15 at 8:26

2 answers

 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

+1

realnumber3012 Aug 10 '15 at 8:30

Philip couling · Accepted Answer · 2015-08-10T08:46:21+0000

Should I miss something incomprehensible, is this not just a simple band?

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