CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
squarewav
squarewav

Reputation: 423

SQL ORDER BY but also by MIN of different col (aka threading)?

Regarding the example data below, it is ORDER BY Type but each resulting group is ORDER BY MIN(Valu) without any aggregation.

  Type Valu
  ---- ----
  B    12  
  B    88  
  B    200 
  B    200 
  A    56  
  A    100 
  A    100 
  C    70  
  C    70

So all records of Type B are listed first because that group has a record containing the lowest Valu (12). Then Type A records because of the record with Valu 56 and then Type C records because of the record with Valu(s) 70.

Is there any way to do this type of sorting in SQL?

To explain further, if I were to do this programmatically, I might first sort records by Type and secondarily by Valu decending. But then I would need to sort groups of records by the Valu of the first record of each group.

So something like:

  SELECT Type, Valu FROM data ORDER BY Type, Valu

gets me most of the way there. But then I want to sort the groups by lowest Valu.

This might be compared to "threading" posts in a forum where posts with the same subject are grouped together but the groups are then sorted by the date of the latest post within the group (although threading forum posts is more complex because sorting is done by reply order whereas in my example I just want to sort numerically by Valu).

Is there any way to do this in SQL? If not, and I need to do it programmatically, is there at least a query that will yield the latest records and any records of the same Type but with a LIMIT so that I do not have to retrieve all records? If there are two records of the same Type but one with a very low Valu and the other with a vary high Valu, a LIMIT clause might exclude the record with the high Valu.

Upvotes: 1

Views: 70

Answers (3)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

You can try using analytic functions if your DBMS supports them (Oracle, MS SQL, Postgres...)

    select Type,
           Valu
      from MyTable
  order by Min(Valu) over (partition by Type),
           Type,
           Valu

Upvotes: 1

lopo
lopo

Reputation: 306

Get All Types an their min values in a subquery and rejoin to table. Then you can order of Type-groups min Value.

SELECT
    table.Type,
    table.value
FROM (
    SELECT 
        Type,
        min(value) AS minValue
    FROM table
    GROUP BY Type
) AS sub
INNER JOIN table
    ON table.Type = sub.Type
ORDER BY sub.minValue ASC, table.Type ASC, table.value ASC

Upvotes: 2

Azar
Azar

Reputation: 1867

SELECT Type, Valu FROM data ORDER BY Type desc, Valu asc

Upvotes: 0

Related Questions