CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
eraelpeha
eraelpeha

Reputation: 419

Bring whole blocks in order

I have a query that looks like

SELECT
    a, b, c, d
FROM
    tab
ORDER BY
    a ASC, b ASC

And my result set looks like

+-----------------------+
|  a  |  b  |  c  |  d  |
+-----------------------+
|  A1 |  B1 |  X  | null|
|  A1 |  B2 |  X  | null|
|  A1 |  B3 |  X  | null|
|  A1 |  B4 |  X  | date|
|  A2 |  B1 |  X  | null|
|  A2 |  B2 |  X  | null|
|  A2 |  B3 |  X  | null|
|  A2 |  B4 |  X  | date|
...

where X can be anything (doesn't matter here). I want to order these blocks A1, A2 etc. by date in column d without changing the order among blocks (so that B1 is still before B2, ...).

Any suggestions? Or is this approach going in the wrong direction?

Upvotes: 1

Views: 48

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658062

This should be much simpler with max() as window function in the ORDER BY clause:

SELECT * 
FROM   tbl
ORDER  BY max(d) OVER (PARTITION BY a), a, b;

Upvotes: 0

valex
valex

Reputation: 24144

If I guess right you want to sort blocks by MAX(D) for whole block (or MIN(d) ?). Try this query then:

SELECT T.* 
FROM T
JOIN (SELECT a, MAX(d) as maxD 
        FROM T 
        GROUP BY a) as T1
  ON T.a=T1.a
ORDER BY T1.maxD,T.a,T.b

SQLFiddle demo

Upvotes: 1

Related Questions