Gen Gen -4 years ago 96
SQL Question

SQL Server 2012 - Using ROW_NUMBER() on DISTINCT

I have a query below.

SELECT DISTINCT
FORMAT(CAST(SchedTi AS DATETIME),'hh:mm tt') AS SchedTimeIn,
FORMAT(CAST(SchedTO AS DATETIME),'hh:mm tt') AS SchedTimeOut
FROM
tblemployee_schedule
ORDER BY
SchedTimeIn


It returns this resultset:

SchedTimeIn SchedTimeOut
01:00 AM 09:00 AM
01:00 AM 10:00 AM
01:00 AM 10:00 PM
01:15 AM 05:15 AM
01:15 AM 10:15 AM
01:30 AM 05:30 AM
01:30 PM 10:30 PM
01:45 AM 05:45 AM


My desired result is:

SchedTimeIn SchedTimeOut ROWNUM
01:00 AM 09:00 AM 1
01:00 AM 10:00 AM 2
01:00 AM 10:00 PM 3
01:15 AM 05:15 AM 4
01:15 AM 10:15 AM 5
01:30 AM 05:30 AM 6
01:30 PM 10:30 PM 7
01:45 AM 05:45 AM 8


I tried the following query:

SELECT DISTINCT
ROW_NUMBER() OVER(ORDER BY SchedTi),
FORMAT(CAST(SchedTi AS DATETIME),'hh:mm tt') AS SchedTimeIn,
FORMAT(CAST(SchedTO AS DATETIME),'hh:mm tt') AS SchedTimeOut
FROM
tblemployee_schedule
ORDER BY
SchedTimeIn


But it returns too many redundant rows and NULL values.

Thank you in advance.

Answer Source

Try the below one,

SELECT * ,ROW_NUMBER() OVER(ORDER BY SchedTimeIn) AS ROWNUM
FROM    (
    SELECT DISTINCT
    FORMAT(CAST(SchedTi AS DATETIME),'hh:mm tt') AS SchedTimeIn,
    FORMAT(CAST(SchedTO AS DATETIME),'hh:mm tt') AS SchedTimeOut
    FROM tblemployee_schedule
)   AS  D
ORDER BY ROWNUM

In your query ROW_NUMBER() produce a new sequence number for each records , so the DISTINCT key will not wok for the same, that's why you are getting too many redundant records. So you can use the ROW_NUMBER() in an outer query to overcome this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download