SQL Row_Number() not sorting dates

Question

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 |
+--------+------------+-------+--------+

What is the datatype on the `TicketDate` column? Could it be a string/varchar? It doesn't look like a datetime based on the sort. — ollie, May 08 '17 at 19:36
Looks Like your `Group By` shouldn't have `tt.TicketDate` in it. Try it without that. — RBarryYoung, May 08 '17 at 19:40
When I use row_number for something like this, I dont use group by or aggregates. I just have where RowNum = 1 — Joe C, May 08 '17 at 19:41
@ollie its a char(8) variable. Is that a string? And if so, can I properly convert that to a date for sorting? — RBC Kyle Bullard, May 08 '17 at 19:42
@RBCKyleBullard Yes, you can [see this answer](http://stackoverflow.com/a/1510131/426671) or [this post](https://dba.stackexchange.com/questions/129854/how-to-convert-this-varchar-to-datetime-format). You'll want to convert it in order to get the correct sorting of your dates — ollie, May 08 '17 at 19:44
Yes, That is a string. You should store date using date data type. Or use ansi format `YYYY-MM-DD` — Juan Carlos Oropeza, May 08 '17 at 19:44
@JuanCarlosOropeza RowNum 13 to be sorted as RowNum 1 so I can call WHERE RowNum = 1 and get the most recent date. — RBC Kyle Bullard, May 08 '17 at 19:44
Well you also are using a `GROUP BY` and you shouldnt. Provide the original data and the desire output so we can help you build the correct query. — Juan Carlos Oropeza, May 08 '17 at 19:46
@JuanCarlosOropeza is there a way to convert it from a string for the result I am looking for? This is not my database I was just asked to write a few queries on it and I am learning on the fly about how it is setup and honestly how to write queries correctly. — RBC Kyle Bullard, May 08 '17 at 19:47
But that doesn't looks like is sort by string neither, so we need the original CREATE TABLE to be sure what is going on. — Juan Carlos Oropeza, May 08 '17 at 19:55
@JuanCarlosOropeza added sample data and desired result to edit. I will check out the link you just sent over. Thanks for all of your help so far by the way. — RBC Kyle Bullard, May 08 '17 at 19:57
@JuanCarlosOropeza it has been updated, sorry, trying to work too fast to get the info you need. — RBC Kyle Bullard, May 08 '17 at 20:01
Please tell me if this is the exact result you are having in your system? http://rextester.com/UUM93496 because as you can see `5/9/2012` should on the top and in your sample output isnt there. So I guess you have several issues together. — Juan Carlos Oropeza, May 08 '17 at 20:04
Very similar yes, I truncated (possibly two different ways before and after the edit) the sample data as I get about 100 rows as there are many TicketDates for each JobNo in the actual db. My sample output has it a RowNum 12. I need 3/27/2015 to be the RowNum 1 output. — RBC Kyle Bullard, May 08 '17 at 20:08

score 2 · Accepted Answer · edited Jan 18 '21 at 12:32

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;

Dont you need add the cast for `MAX( cast(tt.TicketDate as datetime) )`too ? — Juan Carlos Oropeza, May 08 '17 at 20:14
That did the trick! Thank you! I will keep a better lookout in this DB for weird things like strings as dates and whatnot. I really appreciate your help. — RBC Kyle Bullard, May 08 '17 at 20:15
@JuanCarlosOropeza You're correct, I was actually in the process of adding that to my answer — ollie, May 08 '17 at 20:15
@ollie I noticed that before and added it before I ran the query on my end. — RBC Kyle Bullard, May 08 '17 at 20:17

score 1 · Answer 2 · answered May 08 '17 at 20:13

As Ollie suggest you can CAST your string to DATETIME And you dont need the additional Group By

SQL DEMO

;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    
    JOIN  TimeTicketDet AS tt 
      ON o.JobNo = tt.JobNo       
    WHERE
        o.Status = 'Open'
)    
SELECT *
FROM cte3
WHERE RowNum = 1

OUTPUT

Thank you for all of your help and following up with every comment. — RBC Kyle Bullard, May 08 '17 at 20:15

SQL Row_Number() not sorting dates

2 Answers2

Linked