group by max date

[drallabs]

I have one table that has multiple id's and associated dates as well as other fields. I would like to query all the id's with the id's latest date entry and include other fields in this query. The following query selects the one id value and its latest date entry:

SELECT id, MAX(InspectionDt)
FROM table
GROUP BY ID

As I said I would also like to include other fields from this same table in my query which I tried using this query:

select *
from table t1
inner join
(SELECT id, MAX(InspectionDt) As inspectiondt
FROM table
GROUP BY ID) t2 on t1.ID = t2.ID

But I end up with duplicate id values which I dont want.

What query accomplishes this?

Thanks in advance.

 

[gmmastros]

You were close...

select *
from table t1 
inner join
(SELECT id, MAX(InspectionDt) As inspectiondt
 	 FROM table
 	 GROUP BY ID) t2 on t1.ID = t2.ID
                         [!]and t1.InspectionDt = t2.InspectionDt[/!]

You should understand that it is *technically* possible for duplicate ID's with this query too. The only way you would get duplicates though is if the max inspection date has multiple rows for the same id.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drallabs]

thanks is there a way to dump this query into a new table or view I have tried but get an error


Column names in each view or function must be unique. Column name 'id' in view or function 'vwPATSInspectionCurrentDate' is specified more than once.

 

[gmmastros]

Your problem is with the [!]Select *[/!] part. You are joining your table to a derived table that includes id and InspectionDt, but those columns also exist in the main table. Effectively these are duplicate columns. To get around this problem, just get all of the columns from the main table, like this:

select [!]T1.[/!]*
from table t1 
inner join
(SELECT id, MAX(InspectionDt) As inspectiondt
      FROM table
      GROUP BY ID) t2 on t1.ID = t2.ID
                         and t1.InspectionDt = t2.InspectionDt

Now, instead of all columns from all tables, you will only get all columns from the T1 table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom