Home > Mobile >  SQL ORDER BY on Subset of Data in Table
SQL ORDER BY on Subset of Data in Table

Time:03-29

I have a table structure as below:

Table: Details
ID      |  ParentName       |  ChildName
1       |   ParentA         |  ChildA
2       |   ParentA         |  ChildC
3       |   ParentA         |  ChildB
4       |   ParentB         |  ChildL
5       |   ParentB         |  ChildS
6       |   ParentB         |  ChildT
7       |   ParentB         |  ChildM
8       |   ParentB         |  ChildP
9       |   ParentB         |  ChildR
10      |   ParentC         |  ChildZ

I need to sort the ChildName in ascending order only when the ParentName is ParentB, for other ParentName - sorting should not be applied So basically I need to sort only the subset of data ie. when ParentName = ParentB, and other data should remain as it is.

I tried the below query, but it is sorting all records - but I need to sort only for ParentB

SELECT * FROM DETAILS WITH (NOLOCK) ORDER BY ParentName , ChildName

Expected result is: Sorting is done only for ParentName = ParentB, other data is same.

ID      |  ParentName       |  ChildName
1       |   ParentA         |  ChildA
2       |   ParentA         |  ChildC
3       |   ParentA         |  ChildB
4       |   ParentB         |  ChildL
7       |   ParentB         |  ChildM
8       |   ParentB         |  ChildP
9       |   ParentB         |  ChildR
5       |   ParentB         |  ChildS
6       |   ParentB         |  ChildT
10      |   ParentC         |  ChildZ

Any help or advice would be appreciated.

Thanks in advance

CodePudding user response:

You can use a CASE expression in the ORDER BY clause

order by ParentName,
    case when ParentName = 'ParentB' then ChildName end,
    ID
  • Related