4

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

Tom
  • 787
  • 1
  • 12
  • 28

2 Answers2

2

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;
Philip Couling
  • 13,581
  • 5
  • 53
  • 85
  • Thanks for your answer. Please, check results of your query here http://sqlfiddle.com/#!15/f958b/2 user_id=2 should be on the first row (because of 30 days date range), the second row should be user_id=3. It seems like your query is not working like this – Tom Aug 10 '15 at 08:54
  • I dont have a postgres server to hand at this second, but you can add a `desc` if that helps. I've edited the answer. – Philip Couling Aug 10 '15 at 08:59
  • @couling: check the sqlfiddle link: you _do_ have a Postgres server at hand there –  Aug 10 '15 at 09:01
  • @a_horse_with_no_name oh I see. that's not just a pastebin alternative ;-) Ah well. Yes adding `desc` works. – Philip Couling Aug 10 '15 at 09:05
  • 1
    @AlexeyLisikhin: If any of the columns can be NULL, [add `NULLS LAST` to respective `ORDER BY` items](http://stackoverflow.com/a/9511492/939860). – Erwin Brandstetter Aug 10 '15 at 09:34
1
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
realnumber3012
  • 1,062
  • 6
  • 10
  • http://sqlfiddle.com/#!15/eb51d Thanks. But I need order by date range. It can be date range is bigger one, but end date of this date range not latest date in the table – Tom Aug 10 '15 at 08:31
  • New answer is working, thank you! Please write it here (not just put a link), and I will accept it. – Tom Aug 10 '15 at 08:41
  • I tested this code a little more, it is not working like needed :( Please, check this data examples. user_id=3 should be on the second row, but it is on the third row http://sqlfiddle.com/#!15/f958b/1 – Tom Aug 10 '15 at 08:51