CODEWITHSUNDEEP
sqlexcel
SashaSQL
SashaSQL

Reputation: 33

Double sort in Excel or SQL

I have a weird table-sorting issue that I cannot figure out in Excel or SQL.

Here's a table sorted by column 2, "Letter".

Name Letter
Dan A
Moe A
Ted B
Dan C
Joe F
Noe F

What I need is the table sorted by Letter BUT... if there are any duplicates in the "Name" column, I need those grouped--which would break the sorting in column 2. So below, even though the table is sorted by Letter, I want the two Dans together:

Name Letter
Dan A
Dan C
Moe A
Ted B
Joe F
Noe F

Thanks for any help!

Upvotes: 0

Views: 95

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you just want two keys in your order by clause:

order by name, letter

EDIT:

If you want to sort by name based on the lowest letter, then you would use window functions:

order by min(letter) over (partition by name),
         name, letter

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

This Power Query solution (available in Windows Excel 2010+ and Office 365) seems to do what you want on your database.

Please read the comments.

I group by Name and then sort by letter (as well as sort each subGroup by letter).

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Letter", type text}}),

//Group by name
//Sort each subgroup and extract the initial letter
//Generate a Table with each subtable properly sorted
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
        {"All", each _, type table [Name=nullable text, Letter=nullable text]},
        {"SortSubGroup", each Table.Sort(_,"Letter")},
        {"letter1", each List.Sort([Letter]){0}, type text}
        }),

//Sort by initial letter and then by Name
//Remove the unneeded columns and expand the subTables
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"letter1", Order.Ascending}, {"Name", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Name", "All", "letter1"}),
    #"Expanded SortSubGroup" = Table.ExpandTableColumn(#"Removed Columns", "SortSubGroup", {"Name", "Letter"}, {"Name", "Letter"})
in
    #"Expanded SortSubGroup"

enter image description here

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

You didn't really say where in the results you wanted the Dans but let's use a window function to count the number of same names and sort by that initially (descending, meaning the dans will sort nearer the top)

SELECT * 
FROM t
ORDER BY COUNT(*) OVER(PARTITION BY name) DESC, letter

If you have eg two Fred and you want them to sort together and not mix up with the dans, consider

SELECT * 
FROM t
ORDER BY CASE WHEN COUNT(*) OVER(PARTITION BY name) = 1 THEN NULL ELSE name END DESC, letter

This effectively puts unique names "in one basket" that is then sorted by letter and for "non unique names" they clump together by the name and then sort by the letter

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

I would try something like this.

SELECT t.name, t.letter
  FROM table t
  JOIN (SELECT table.name, MIN(table.letter) AS min_letter_by_name
          FROM table GROUP BY table.name)
    ON t.name = table.name
  ORDER BY min_letter_by_name, name, letter

Upvotes: 2

Related Questions