CODEWITHSUNDEEP
postgresqlsorting
John
John

Reputation: 426

Sorting SQL table by multiple columns

Assume I have this table named extable

|--A--|--B--|--C--|
|-----|-----|-----|
|01:21| 4-1 | no  |
|01:28| 4-2 | yes |
|05:53| 3-5 | yes |
|03:34| 9-1 | yes |
|18:48| 9-1 | yes |
|13:01| 9-1 | yes |
|00:17| 7-4 | yes |
|14:24| 4-1 | no  |
|18:19| 4-1 | yes |
|08:02| 7-4 | yes |
|15:21| 7-4 | no  |
|21:23| 3-5 | no  |
|02:57| 4-2 | no  |
|21:03| 4-2 | yes |
|11:31| 4-1 | no  |

How would I sort it by time, column A, then column B, then column C, like the following:

|--A--|--B--|--C--|
|-----|-----|-----|
|05:53| 3-5 | yes |
|21:23| 3-5 | no  |
|18:19| 4-1 | yes |
|14:24| 4-1 | no  |
|21:03| 4-2 | yes |
|02:57| 4-2 | no  |
|08:02| 7-4 | yes |
|15:21| 7-4 | no  |
|18:48| 9-1 | yes |
|21:56| 9-1 | no  |

I want to be able to sort it by the lowest to highest in column B. Then, I want yes to be before the no option for each entry in column B. The timestamp in column A will be the latest of the yes and no entries. I feel like me explaining it is a lot less effective than the example above.

I know I can do this: ORDER BY column B, column C, column A;, but this will pull every single entry out of the original table. I only want the latest ones by the timestamp in column A, if that makes sense. Notice how 9-1 has four entries in the original table, but only the latest yes and no options were pulled for it.

Thanks

Upvotes: 0

Views: 305

Answers (1)

JNevill
JNevill

Reputation: 50019

You have two requirement: 1) Order the results as listed 2) Keep only the most recent yes/no values for each column "B"

The ORDER BY clause is pretty straightforward. The only change is sticking "DESC" after the C field in the clause so that yes shows up first.

The second requirement can be solved using a Window Function to group the rows in the result set by B and C, and then sort it by A numbering the results in each group. Then we just keep anything ranked as 1

Something like the following should work:

SELECT A,B,C
FROM
    (
        SELECT 
            A,
            B,
            C,
            ROW_NUMBER() OVER (PARTITION BY B, C ORDER BY A DESC) as keeprank
        FROM <table>
    ) t1
WHERE keeprank = 1
ORDER BY A, B, C DESC

Upvotes: 1

Related Questions