Good day

Tell me how to sort by several conditions (SQL ORDER BY)

let's say a table

A (row_num) B (attribute), C (name)

needed, sorted alphabetically by the 'C' (name) column, but so that the first entries are those where B = null (or maybe not only null, but some other parameter should be a priority)

base postgresql

условно должно выйти: 1 null Петя --идет раньше Андрея т.к. null 2 33 Андрей 3 20 Петр 4 454 Яна
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

3 answers 3

 select * from table order by case when attr is null then 0 else 1 end, name

    You can do the following:

     SELECT * FROM tbl ORDER BY attr IS NOT NULL, name
    • live and learn - rjhdby
    • it turns out that it will be sorted by attr, and then by name, inside the same attr? - nonament pm
    • @nonament, yes. Need another logic? - cheops pm
    • Yes, you need to take the name as the basis for sorting, except for one BUT: positions where attr null - should be first - nonament
    • @nonament, then you need to swap the columns in ORDER BY. Corrected the answer. - cheops
     SELECT * FROM (SELECT * FROM table WHERE B IS NULL order by C) t1 union ALL SELECT * FROM (SELECT * FROM table WHERE NOT B IS NULL order by C) t2
    • It will be cumbersome, given that I have it inside rank () over PARTITION BY over ... and a ball with a bunch of conditions, does the union guarantee order security? - nonament 7:44 pm
    • @nonament is a good question. I think so, why would he have to break it? - rjhdby
    • I do not trust requests without order by at the end, if you need a clear sequence that you need to pass on to higher requests) if nothing else comes to mind. I will try to add row_numer to both unions, the second one will continue where the first one ends - nonament
    • @nonament is not constructive. union sticks together two sorted samples, with which fright does it have to mix them? - rjhdby