Sorting query results in two columns

Question

I have this table:

Reply_ID | Fk_Post_ID 10 | 5 9 | 6 8 | 5 7 | 9 6 | 5 5 | 9 4 | 7 

I need a query that retrieves the entries in the following order template. it searches for the record with the highest reply_ID and then retrieves all the records that have the same Fk_Post_ID. something like that:

 Reply_ID | Fk_Post_ID 10 | 5 8 | 5 6 | 5 9 | 6 7 | 9 5 | 9 4 | 7 

Answer 1

 CREATE TABLE #YourTable ( Reply_ID INT, fk_Post_ID INT ) INSERT INTO #YourTable VALUES (10, 5) INSERT INTO #YourTable VALUES (9, 6) INSERT INTO #YourTable VALUES (8, 5) INSERT INTO #YourTable VALUES (7, 9) INSERT INTO #YourTable VALUES (6, 5) SELECT t1.Reply_ID, t1.fk_Post_ID FROM #YourTable t1 JOIN ( SELECT MAX(Reply_ID) AS Max_Reply_ID, fk_Post_ID FROM #YourTable GROUP BY fk_Post_ID ) t2 ON t2.fk_Post_ID = t1.fk_Post_ID ORDER BY t2.Max_Reply_ID DESC, t1.Reply_ID DESC 

Sql Fiddle Here

Answer 2

You can use CASE in ORDER BY :

 .... ORDER BY CASE WHEN Fk_Post_ID=( SELECT MIN(Fk_Post_ID) FROM dbo.Table WHERE Reply_ID=(SELECT MAX(Reply_ID)FROM dbo.Table) ) THEN 0 ELSE 1 END ASC , Reply_ID DESC , Fk_Post_ID ASC 

Here's the fiddle: http://sqlfiddle.com/#!3/45f20/7/0

Answer 3

If your DBMS has window functions, this will also solve it:

 Select Reply_ID, FK_Post_ID From yourTable a Order By Max(Reply_ID) Over (Partition By FK_Post_ID) Desc, Reply_ID Desc 

http://sqlfiddle.com/#!3/4be0c/9/0