CODEWITHSUNDEEP
sqlsql-order-by
Shamshiel
Shamshiel

Reputation: 2211

How do I return rows in groups by certain values?

I want my query to return the rows of a table in groups where a column contains specific values. After I got the rows ordered in the groups I want to be able to order them by name.

Example Table

 - Id    - Name    - Group       
 - 1       George    Group_2_1
 - 2       Alfred    Group_2_2
 - 3       Eric      Group_3
 - 4       Mary      Group_1_2
 - 5       Jon       Group_1_1

I want them ordered by their group and after that ordered by their name

 - Id    - Name    - Group    
 - 1       Jon       Group_1_1
 - 2       Mary      Group_1_2
 - 3       Alfred    Group_2_2   
 - 4       George    Group_2_1
 - 5       Eric      Group_3

I found this SQL-Query-Snippet

ORDER BY CASE WHEN Group LIKE '%Group_1%' THEN 1 ELSE 2 END, Group

but it is not enough. The result is only grouped by the first group (obviously) but I can't extend it to order the second group because it is in the same column.

Please don't get confused by the example. I just want to be able to group certain rows and put them in front of the results. I want a result that has all rows containing group 1 in the top, containing group 2 in the middle and containing group 3 in the bottom. The values are not "Group_1_1" or something like that. They are just some strings and I want certain strings to be always in the first row (group 1) and some always below group 1

Upvotes: 0

Views: 935

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Edits for mssql

If there is ANY instance of 3 underscores then the following simply won't work. However if there is the possibility of Group_12_6 or Group_21_1 then this approach may be worth trying.

It removes Group_ or Group from the string, leaving 1_1 or 12_6 or 21_1 then it replaces the remaining underscore with . giving 1.1 or 12.6 or 21.1 and casts this to decimal.

All utterly dependent of the consistency of those group names.

SELECT
      id
    , name
    , [Group]
FROM YourData
ORDER BY
      CAST(REPLACE(REPLACE(REPLACE([Group], 'Group_', ''), 'Group', ''), '_', '.') AS decimal(12,3))
    , name

I'm really hoping you do not have a column called [Group] but if you do it has to be referenced as [Group] or "Group". Test result:

| ID |   NAME |     GROUP |
|----|--------|-----------|
|  1 |    Jon | Group_1_1 |
|  2 |   Mary | Group_1_2 |
|  4 | George | Group_2_1 |
|  3 | Alfred | Group_2_2 |
|  5 |   Eric |   Group_3 |

see http://sqlfiddle.com/#!3/e95b07/1

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

The problem here seems to be that some of your group names have an extra underscore, otherwise you could just order by the Group and all would be good. You could probably do something like this to work around this?

WITH Data AS (
SELECT 'Group1_1' AS Value
UNION 
SELECT 'Group_3_2' AS Value
UNION
SELECT 'Group_2_2' AS Value
UNION
SELECT 'Group_3_1' AS Value
)
SELECT * FROM Data ORDER BY CASE WHEN Value LIKE 'Group_%' THEN SUBSTRING(Value, 7, 10) ELSE SUBSTRING(Value, 6, 10) END;

Results:

Value
Group1_1
Group_2_2
Group_3_1
Group_3_2

---- EDIT ----

Okay, seeing as your example isn't really an "example" it sounds like you are going to need a really, REALLY long case statement. You could do something like this (using the original Group_1_1, Group_2_2 codes) that would extend to different values. The key is that a CASE statement works from left to right and a value is assigned to the first case that matches:

ORDER BY
    CASE
        WHEN [Group] = 'Group_1_1' THEN 1
        WHEN [Group] = 'Group_1_2' THEN 2
        WHEN [Group] LIKE 'Group_1_%' THEN 3
        WHEN [Group] = 'Group_2_1' THEN 4
        WHEN [Group] = 'Group_2_2' THEN 5
        WHEN [Group] LIKE 'Group_2_%' THEN 6
       etc.
    END;

Obviously that's very generic and depends on what the actual values are in your database.

Upvotes: 1

Related Questions