ordering query results with two columns

sql, sql-server

Solution

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

Problem

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 retrieves records in the following order pattern. it searches for the record with the highest reply_ID then retrieves all records having the same Fk_Post_ID. something like this: ``` Reply_ID | Fk_Post_ID 10 | 5 8 | 5 6 | 5 9 | 6 7 | 9 5 | 9 4 | 7 ```

Original source