CODEWITHSUNDEEP
sqlsql-servermax
badgerseatfrogs
badgerseatfrogs

Reputation: 107

SELECT MAX DATE when records have identical dates

I have table with many rows that have the same date. I want to query the max (latest) data but if the dates are the same, only return one. It doesn't matter which record but I could use the latest (GID).

I currently have this to to get the latest date:

SELECT ProjectNum,DateSaved,FilePath ,GID
FROM dbo.master_m_table
AS [alldata]
WHERE DateSaved = (
        SELECT MAX(DateSaved)
        FROM dbo.master_m_table
        WHERE FilePath = [alldata].FilePath)

But if there are two identical dates then it will return both.

to better explain my question here is the data:

ProjectNum  DateSaved   FilePath    GID
12345   01.01.2015             a    1
12345   01.01.2015             a    2
12345   01.01.2015             a    88
12345   01.01.2015             c    104
12345   25.01.2015             c    101
12345   25.01.2015             c    68

...and I want the latest date unless the dates are the same and then I want the max GID.

example result:

ProjectNum  DateSaved   FilePath    GID
12345       01.01.2015         a    88
12345       25.01.2015         c    101

Upvotes: 0

Views: 2587

Answers (3)

James Z
James Z

Reputation: 12317

Looks like you're looking for this:

SELECT ProjectNum,DateSaved,FilePath,GID
FROM (
  select 
    ProjectNum,DateSaved,FilePath,GID, 
    row_number() over 
        (partition by FilePath order by DateSaved desc, GID desc) as RN
  from dbo.master_m_table
) X
where RN = 1

Row number will create numbers for the rows, for each FilePath separately, and order it first by DateSaved and then GID descending and then outer part will then only pick the rows with number 1. You might also need ProjectNum in partition by part.

Example in SQL Fiddle

Upvotes: 1

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

try this

SELECT top 1 ProjectNum,DateSaved,FilePath ,GID
FROM dbo.master_m_table
AS [alldata]
order by DateSaved desc, GID desc

or

SELECT top 1 ProjectNum,DateSaved,FilePath ,GID
FROM dbo.master_m_table
AS [alldata]
WHERE DateSaved = (
        SELECT MAX(DateSaved)
        FROM dbo.master_m_table
        WHERE FilePath = [alldata].FilePath)
order by GID desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Just use top and order by:

select top 1 t.*
from dbo.master_m_table t
order by t.datesaved desc;

If you wanted all of them, you can use with ties.

I'm not sure what you mean by "the latest GUID". You can add more columns to the order by to handle ties.

Upvotes: 0

Related Questions