CODEWITHSUNDEEP
sqlsql-server-2008
PMC
PMC

Reputation: 4756

SQL ORDER BY with grouping

I have the following query

SELECT Id, Request, BookingDate, BookingId FROM Table ORDER BY Request DESC, Date

If a row has a similar ForeignKeyId, I would like that to go in before the next ordered row like:

Request         Date  ForeignKeyId
Request3    01-Jun-11   56
Request2    03-Jun-11   89
NULL        03-Jun-11   89
Request1    05-Jun-11   11
NULL        20-Jul-11   57

I have been looking at RANK and OVER but haven't found a simple fix.

EDIT

I've edited above to show the actual fields and pasted data using the following query from Andomar's answer

select  *
from    (
        select  row_number() over (partition by BookingId order by Request DESC) rn 
        ,       Request, BookingDate, BookingID
        from    Table
        WHERE Date = '28 aug 11'
        ) G
order by
     rn   
,       Request DESC, BookingDate 

1   ffffff  23/01/2011 15:57    350821
1   ddddddd 10/01/2011 16:28    348856
1   ccccccc 13/09/2010 14:44    338120
1   aaaaaaaaaa  21/05/2011 20:21    364422
1   123 17/09/2010 16:32    339202
1       NULL    NULL
2   gggggg  08/12/2010 14:39    346634
2       NULL    NULL
2       17/09/2010 16:32    339202
2   NULL    10/04/2011 15:08    361066
2   NULL    02/05/2011 14:12    362619
2   NULL    11/06/2011 13:55    366082
3       NULL    NULL
3       16/10/2010 13:06    343023
3       22/10/2010 10:35    343479
3       30/04/2011 10:49    362435

The booking ID's 339202 should appear next to each other but don't

Upvotes: 4

Views: 139

Answers (1)

Andomar
Andomar

Reputation: 238048

You could partition by ForeignKeyId, then sort each second or lower row below their "head". With the "head" defined as the first row for that ForeignKeyId. Example, sorting on Request:

; with  numbered as 
        (
        select  row_number() over (partition by ForeignKeyID order by Request) rn
        ,       *
        from @t
        )
select  *
from    numbered n1
order by
        (
        select  Request 
        from    numbered n2 
        where   n2.ForeignKeyID = n1.ForeignKeyID 
                and n2.rn = 1
        )
,       n1.Request

The subquery is required because SQL Server doesn't allow row_number in an order by clause.

Full example at SE Data.

Upvotes: 2

Related Questions