Query picking randomly when using rownumber()

Question

Hi All,

Here is my sample script :

TMP1 have 2 rows with identical values of the field used by Partition , thus TMP2 is picking randomly when doing RN=1.
Can this be fix ?

WITH TMP1 AS (
SELECT
distinct [HERO ID],
[Service Unit Name],
[Location Facility ID],
[Health Service],
Department,
,Division, Specialty,
[Effective To],
[Effective from],
[Secure Org Flag],
, ISNULL([Service Unit Hierarchy],99) AS [Service Unit Hierarchy]
FROM VIEWMAPPING
), TMP2 AS (
SELECT *, RN=ROW_NUMBER() OVER(PARTITION BY [HERO ID] ORDER BY [Service Unit Hierarchy] ASC, [Effective To] Desc, [Effective From] Desc, [Location Facility ID] ) FROM TMP1
)
SELECT [HERO ID],
[Service Unit Name],
[Location Facility ID],
Department,
Division, Specialty,
[Effective To],
[Effective from],
[Secure Org Flag]
FROM TMP2 WHERE RN=1

Thanks

Answer 1

thus TMP2 is picking randomly when doing RN=1.

Randomly in which way? By your screenshot the resultset have two identical rows, so how can one say it's "randomly"; you get always the same.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

Hi @ASHMITP ,

Role of PARTITION BY:
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.
If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
It would be a good idea for you to post your table structure and your expected output so we can understand your needs.

Best regards
Niko

Answer 3

Your order is not distinct. This results in "randomly" picking a row.

You need to change this to a sort which returns the same row every time.

ORDER BY [Service Unit Hierarchy] ASC, [Effective To] Desc, [Effective From] Desc, [Location Facility ID]