jeudi 13 août 2015

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.

  • edited -

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



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire