CODEWITHSUNDEEP
sqloracle-databasesql-order-by
RNJ
RNJ

Reputation: 15552

Order by within an order by in SQL

Im a bit stuck on an order by column. Ive got some good answers to another query from stack overflow but now just need to order it correctly.

Consider I have these results (unordered)

|LloydA|20|
|LloydB|0 |
|LloydC|5 |
|JonesA|10|
|JonesB|0 |
|ZuberA|0 |
|ZuberB|10|

then want to order them first by column two but also withing it column 1 so the results are

|LloydA|20|
|LloydC|5 |
|LloydB|0 |
|JonesA|10|
|JonesB|0 |
|ZuberB|10|
|ZuberA|0 |

Because the 20 is hte maximum I want to show this row at the top and then show all the ones with the same start of name after it (ie all the Lloyd..)

I'm thinking a subselect with the order by but Im struggling. Has anyone got any tips.

Upvotes: 1

Views: 164

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Based on your previous SO question you don't need to extract the name, you simply have to add a GROUP MAX:

-- copied from the accepted answer
select p.* from people p  
join (select p1.first_name, p1.last_name 
      from people p1 where p1.id = 17
     )un
  on un.first_name = p.first_name 
where p.last_name like un.last_name || '%'
-- ADDED
order by  -- find the MAX for each name
   max(col2) over (partition by un.last_name),
   last_name

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220877

Here's a query that produces the results that you need:

WITH 
  -- "t" contains the raw data
  t(name, score) AS (
    SELECT 'Lloyd0', 20 FROM DUAL UNION ALL
    SELECT 'Lloyd1', 0  FROM DUAL UNION ALL
    SELECT 'Lloyd2', 5  FROM DUAL UNION ALL
    SELECT 'Jones0', 10 FROM DUAL UNION ALL
    SELECT 'Jones1', 0  FROM DUAL UNION ALL
    SELECT 'Zuber0', 0  FROM DUAL UNION ALL
    SELECT 'Zuber1', 10 FROM DUAL
  ),

  -- "u" generates the "name prefix" by removing the numbers form the names
  u(name, score, name_prefix) AS (
    SELECT name, score, regexp_replace(name, '\d+', '')
    FROM t
  ),

  -- "v" generates the max score per name_prefix ("group_rank") for each "group"
  v(name, score, group_rank, name_prefix) AS (
    SELECT name, score, MAX(score) OVER (PARTITION BY name_prefix), name_prefix
    FROM u
  )
SELECT name, score
FROM v
ORDER BY 

  -- Order by the group's rank first
  group_rank DESC, 

  -- Order equally ranked groups by name
  name_prefix ASC, 

  -- Order entries within each group by score
  score DESC

Of course, you don't need to use common table expressions for this. This could be done with derived tables, or views as well, or by repeating certain expressions.

SQLFiddle here

Note: Your original question referred to Lloyd0 instead of LloydA. It isn't quite clear what the "interesting" part of a name really is (e.g. Lloyd), and what the "non-interesting" part is (e.g. A or 0). But I suspect that doesn't really matter for the answer.

Upvotes: 1

Related Questions