Nested SQL Server Query Max Date

Question

Ladies and Gents,

I need to write a query that grabs data from a view, but I'm not sure how to go about this. The issue is there is really no key and there are two fields I'm concerned with that will control what rows I need to retrieve.

The view looks something like this:

Category   columna   columnb   uploaddate 
-----------------------------------------------------
   a        value     value    1/30/2013 04:04:04:000 
   a        value     value    1/29/2013 04:04:04:000 
   b        value     value    1/28/2013 01:23:04:000 
   b        value     value    1/30/2013 04:04:04:000 
   b        value     value    1/30/2013 04:04:04:000 
   c        value     value    1/30/2013 01:01:01:000 
   c        value     value    1/30/2013 01:01:01:000

What I need to retrieve is all rows for each unique category and the newest uploaddate. So in the example above I would get 1 row for category a which would have the newest uploaddate. Category b would have 2 rows which have the 1/30/2013 date. Category c would have two rows also.

I also need to just compare the date of upload, not the time. As the loading can take a couple seconds. I was trying to use max date but it would only grab the time to the second.

Any guidance/thoughts would be great.

Thanks!

EDIT:

Here is what I threw together so far and I think it's close but it's not working yet and I doubt this is the most efficient way to do this.

select 
    * 
from 
    VIEW c 
    INNER JOIN 
    ( 
        SELECT 
            Category, 
            MAX(CONVERT(DateTime, Convert(VarChar, UploadDate, 101))) as maxuploaddate  
        FROM 
            View 
        GROUP BY 
            Category, 
            UploadDate 
    ) temp ON temp.Category = c.Category AND CONVERT(VarChar, UploadDate, 101) = temp.maxuploaddate

The problem lies in the nested selected statement as it's still grabbing all combinations of Category and Upload date. Is there a way to do a distinct on the Category and UploadDate, just getting the newest combination?

Thanks Again

So there is SQL Server in your title and there *was* a plsql tag. What will it be? — Jacco, Jan 31 '13 at 21:43

score 1 · Answer 1 · answered Jan 31 '13 at 21:22

If you want to do this to the nearest date, you need to convert to a date first. In SQL Server syntax:

select *
from (select category, columna, columnb, uploaddate,
             rank() over ( partition by category order by cast(uploaddate as date) desc) as seqnum
      from view
     ) v
where seqnum = 1

In Oracle syntax:

select *
from (select category, columna, columnb, uploaddate,
             rank() over ( partition by category order by to_char(uploaddate, 'YYYY-MM-DD') desc) as seqnum
      from view
     ) v
where seqnum = 1

Because you want ties, these use rank() instead of row_number().

score 1 · Accepted Answer · answered Feb 01 '13 at 14:09

1

Your query is close, you have a mistake in the group by. I'd also get rid of the date conversions; date comparisons work fine.

select 
    * 
from 
    VIEW c 
    INNER JOIN 
    ( 
        SELECT 
            Category, 
            MAX(UploadDate) as maxuploaddate  
        FROM 
            View 
        GROUP BY 
            Category
    ) temp ON temp.Category = c.Category AND UploadDate = temp.maxuploaddate

answered Feb 01 '13 at 14:09

Jeff Siver

7,434
30
32

Ahh thank you, the group by on maxuploadate was the issue. thanks brother. – Tim Bassett Feb 01 '13 at 15:17

score 0 · Answer 3 · edited May 23 '17 at 12:17

In Oracle you can use Rank() to achieve this. Rank() creates a duplicate number if the same criteria are met.

Edit: And you can use Trunc() to "trim" the time from the uploaddate.

select *
from (select category, columna, columnb, uploaddate,
      rank() over ( partition by category order by trunc(uploaddate) desc) rank
      from view)
where rank = 1

Also Dense_Rank() exists, which won't create duplicate numbers. So this is not applicable here. See this question for more info on the differences.

Nested SQL Server Query Max Date

3 Answers3