pise
pise

Reputation: 865

Group by, Order by in derived table and Row Number together in SQL Server

I want that first data should be sorted using id desc, then grouped by and then row number finally. I am going to use the table in data-tables pagination.

I want to display Lastname and Designation of employee according to their emp id descending, means first 10 in data tables are the one who joined recently.

Below is my query but SQL Server does not allow order by in a derived table but I cannot put order by in outer last table because that will disturb my row id number and correct data will not be displayed in pagination.

SELECT 
    lastname, designation, 
    row_number() OVER (ORDER BY @@rowcount) AS RowNumber
FROM 
   (SELECT 
        MAX (id), lastname, designation
    FROM employees
    GROUP BY lastname, designation
    ORDER BY MAX(id) DESC) r WHERE r.RowNumber between 1 and 2;

Output:

ID  LastName    Designation
----------------------------
1 | ABC Java  |  Developer
2 | EFG Dot   |  Developer
3 | HIJ PHP   |  Developer
4 | PQR Sql   |  Developer

Required output but without ID

ID  LastName    Designation        RowNumber
-----------------------------------------------
 4 |    PQR Sql   |  Developer       |    1
 3 |    HIJ PHP   |  Developer       |    2

The query shown above is not working in SQL Server

Thanks

Upvotes: 0

Views: 384

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Is this what you want?

SELECT lastname, designation,
       row_number() over (order by max(id) desc) as rownum
FROM employees
GROUP BY lastname, designation
ORDER BY MAX(id) DESC;

Upvotes: 2

Related Questions