Reputation: 865
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
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