SQL ORDER BY grouped

Question

I have the following request

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

If the line has a similar ForeignKeyId, I would like it to go before the next ordered line, for example:

 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 watched RANK and OVER, but did not find a simple solution.

EDIT

I edited above to show actual fields and inserted 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 

Booking ID 339202 should appear next to each other but not

Answer 1

You can partition from ForeignKeyId, and then sort every second or bottom line below your "head". With a "head" defined as the first line for this ForeignKeyId . Example, sorting by 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 

A subquery is necessary because SQL Server does not allow row_number in the order by clause.

Full example in SE Data .