.

rownumbers in SQL

Working with Row numbers:

SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(ORDER BY DOB DESC) AS Row
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

To partition as well (by e.g. ward, so that each ward has a row 1…xxx)

SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(PARTITION BY WardId ORDER BY DOB DESC) AS Row
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

You can also rank in a similar way – one rank no per grouping (ward)
Also consider Dense_Rank but it uses more resource intensive

SELECT PatientId, FirstName, LastName, Ranking,
rank() OVER(ORDER BY WardId) AS Ranking
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

To allow for paging:

WITH MyTable (PatientId, FirstName, LastName, Risk) AS
(
SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(PARTITION BY WardId ORDER BY DOB DESC) AS RowNumber
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;
) 
SELECT PatientId, FirstName, LastName, Risk
FROM MyTable
WHERE RowNumber BETWEEN 50 AND 60;

ntiling (e.g. breaking it in 200 amount of tiles):

SELECT PatientId, FirstName, LastName, Ranking,
ntile(200) OVER(ORDER BY WardId) AS Ranking
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

What's your thoughts on this?

*

Protected by WP Anti Spam