CODEWITHSUNDEEP
sqlpostgresql
Secret
Secret

Reputation: 3358

SQL order by alternating

Is there a way to order sql in an alternating way (intersplicing highest and lowest results)

For example, order by year descending results in something like:

2016
2015
2014
2013

order by year ascending results in something like

1990
1991
1992
1993

What I want to achieve is:

2016
1990
2015
1991

There is probably a manual way to do this (i.e. sort and limit then UNIONthe two result sets), but apart from that, does SQL (in my case Postgres) support this natively?

Upvotes: 0

Views: 634

Answers (2)

Pham X. Bach
Pham X. Bach

Reputation: 5442

Assume that your table table_name that have column name year_column (If you have a query, change table_name to your query and same to year_column)

Then you could use this:

SELECT 
    ROW_NUMBER() OVER (ORDER BY year_column DESC) rnm_desc,
    ROW_NUMBER() OVER (ORDER BY year_column ASC) rnm_asc,
    t.*
FROM 
 table_name t   
ORDER BY 
    LEAST(rnm_desc, rnm_asc), 
    year_column DESC;

Upvotes: 3

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

select      *

from        t

order by    least (row_number () over (order by yr asc),row_number () over (order by yr desc))
           ,yr desc
;

Upvotes: 0

Related Questions