Get Max Date 2

[dvannoy]

I have a simple table with about 6 fields.

One of the fields is called Code.

Code Date
-----------------------
12345 12/10/10
12345 12/01/10
12345 11/30/10

so what I need to do is, if the code is the same say for five records, I only want one record to show that has the max date. so in the above case, what I want to show would be

code 12345 date 12/10/10

any help on this would be appreciated.

Thanks

 

[gmmastros]

Select Code, Max(Date) As MaxDate
From   YourTable
Group By Code

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

 

[bborissov]

In that case:

SELECT Code, MAX(Date) AS Date
FROM YourTable
GROUP BY Code
[code]

But if you have more than one record for this date and you want also other fields, yjem:
If you use SQL Server 2005 or 2008
[code]
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Date DESC) AS RowNumb
             FROM YourTable) YrTbl
WHERE RowNumb = 1

(this is not tested )))



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[dvannoy]

OKAY,

ran into another snag.. the UOM field could be different so even if the code is the same I need it to look at the uom. so with these three records below...for the uom=10 the date of 10/01/10 would show and the uom=20 would also show.

code date uom
-----------------------
1234 10/01/10 10
1234 09/01/10 10
1234 10/01/10 20

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (Select Code, Max(Date) As MaxDate
                   From   YourTable
            Group By Code) Tbl1
ON YourTable.Code = Tbl1.Code AND
   YourTable.Date = Tbl1.Date

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[dvannoy]

I'm getting an error saying...

the multi-part identifier tbl1.code could not be bound
the multi-part identifier tbl1.date could not be bound

 

[bborissov]

Sorry, typo:

SELECT YourTable.*
FROM YourTable
INNER JOIN (Select Code, Max(Date) As MaxDate
                   From   @YourTable
            Group By Code) Tbl1
ON YourTable.Code = Tbl1.Code AND
   YourTable.Date = Tbl1.MaxDate

And BTW you should rename the fields and the table I used here, so they match your real table and field names.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.