RBC Kyle Bullard RBC Kyle Bullard - 18 days ago 7
SQL Question

SQL Row_Number() not sorting dates

I have a query in which I am trying to get the most recent date from my

ROW_NUMBER()
selection. I have tried both
MAX()
and
DESC
in my
ORDER BY
clause. It does not show the most recent date as
RowNum 1
.

This is my query:

;WITH cte3 AS
(
SELECT
o.PartNo,
o.JobNo,
MAX(tt.TicketDate) as rawr,
ROW_NUMBER() OVER (PARTITION BY o.JobNo, o.PartNo
ORDER BY tt.TicketDate DESC) as RowNum
FROM
OrderDet AS o
INNER JOIN
TimeTicketDet AS tt ON o.JobNo = tt.JobNo
WHERE
o.Status = 'Open'
GROUP BY
tt.TicketDate, o.JobNo, o.PartNo
)
SELECT *
FROM cte3


When I get it giving me the correct results, I will add a
WHERE RowNum = 1
in the cte query.

With my current query, this is the result:

+--------+-------+-----------+--------+
| PartNo | JobNo | rawr | RowNum |
+--------+-------+-----------+--------+
| 1234 | 20 | 5/30/2012 | 1 |
| 1234 | 20 | 5/29/2012 | 2 |
| 1234 | 20 | 5/25/2012 | 3 |
| 1234 | 20 | 5/24/2012 | 4 |
| 1234 | 20 | 5/23/2012 | 5 |
| 1234 | 20 | 5/22/2012 | 6 |
| 1234 | 20 | 5/16/2012 | 7 |
| 1234 | 20 | 5/15/2012 | 8 |
| 1234 | 20 | 5/14/2012 | 9 |
| 1234 | 20 | 5/11/2012 | 10 |
| 1234 | 20 | 5/10/2012 | 11 |
| 1234 | 20 | 5/9/2012 | 12 |
| 1234 | 20 | 3/27/2015 | 13 |
| 1234 | 20 | 1/3/2013 | 14 |
| 1234 | 20 | 1/2/2013 | 15 |
+--------+-------+-----------+--------+


RowNum = 13
is the most recent date. Am I organizing my sorts incorrectly or incorrectly converting my dates?

EDIT:

TimeTicketDet Table Sample Data:

+------------+-------+
| TicketDate | JobNo |
+------------+-------+
| 5/9/2012 | 20 |
| 5/10/2012 | 20 |
| 5/24/2012 | 20 |
| 3/27/2015 | 20 |
| 5/22/2012 | 20 |
| 5/10/2012 | 20 |
| 5/11/2012 | 20 |
| 5/9/2012 | 100 |
| 5/10/2012 | 100 |
| 5/24/2012 | 100 |
| 3/27/2015 | 100 |
| 5/22/2012 | 100 |
| 5/10/2012 | 100 |
| 5/11/2012 | 100 |
+------------+-------+


OrderDet Table Sample Data:

+--------+--------+-------+
| PartNo | Status | JobNo |
+--------+--------+-------+
| 1234 | Open | 20 |
| 1234 | Open | 100 |
+--------+--------+-------+


Desired Result:

+--------+------------+-------+--------+
| PartNo | TicketDate | JobNo | RowNum |
+--------+------------+-------+--------+
| 1234 | 3/27/2015 | 20 | 1 |
| 1234 | 3/27/2015 | 100 | 1 |
+--------+------------+-------+--------+

Answer

As I mentioned in my comment, since your TicketDate column is a char, you need to convert it to a datetime in order to sort it by actual date. Right now, you are sorting it by string value which isn't correct.

I'd recommend changing your query to something like this:

;WITH cte3 AS
(       
    SELECT
        o.PartNo, 
        o.JobNo,
        MAX(tt.TicketDate) as rawr,
        ROW_NUMBER() OVER (PARTITION BY o.JobNo, o.PartNo 
                           ORDER BY cast(tt.TicketDate as datetime) DESC) as RowNum    
    FROM
        OrderDet AS o    
    INNER JOIN 
        TimeTicketDet AS tt ON o.JobNo = tt.JobNo       
    WHERE
        o.Status = 'Open'
    GROUP BY
        cast(tt.TicketDate as datetime), o.JobNo, o.PartNo
)    
SELECT *
FROM cte3
where RowNum = 1;

Here is a demo. By casting your char to a datetime in your row_number you will be sorting the data by date instead of string.

Additionally, you don't really need the max() and the GROUP BY since by casting the TicketDate to a datetime you will return the correct row:

;WITH cte3 AS
(       
    SELECT
        o.PartNo, 
        o.JobNo,
        tt.TicketDate as rawr,
        ROW_NUMBER() OVER (PARTITION BY o.JobNo, o.PartNo 
                           ORDER BY cast(tt.TicketDate as datetime) DESC) as RowNum    
    FROM
        #OrderDet AS o    
    INNER JOIN 
        #TimeTicketDet AS tt ON o.JobNo = tt.JobNo       
    WHERE
        o.Status = 'Open'
)    
SELECT *
FROM cte3
where RowNum =1;