select latest record of an item

[aneeka]

Good day!

Anyone knows how to select latest record of an item..here's the scenario.
I have records with the same data except for the date.

Example:

product_code productname item_code date
PR-123 abc TY-01 2011-10-11
PR-123 abc TY-02 2011-10-11
PR-123 abc TY-01 2011-10-13
PR-123 abc TY-02 2011-10-13
PR-123 abc TY-01 2011-10-16
PR-123 abc TY-02 2011-10-16

I need to select this result:
PR-123 abc TY-01 2011-10-16
PR-123 abc TY-02 2011-10-16

Please help me to generate the select statement for this.
Thanks in advance.

[pritaeas]

This should do it:

SELECT * FROM table GROUP BY item_code ORDER BY item_code, date DESC

[adam_k]

@pritaeas this will sort the records descending, but won't select only the latest date. Edit: It won't do even that as you are grouping by item_code and then sorting by item_code and then by date.

This will select only the latest date

select * from table 
inner join (select max(date) as date from table) a 
on table.date = a.date

[aneeka]

Thanks for the replies..i tried the first one but results with errors..
then the second one..i tried it but it results to one record only.

i need to find latest record of many records.

Example:
PR-123 abc TY-01 2011-10-11
PR-123 abc TY-02 2011-10-11
PR-123 abc TY-01 2011-10-13
PR-123 abc TY-02 2011-10-13
PR-123 abc TY-01 2011-10-16
PR-123 abc TY-02 2011-10-16
PR-124 abd TY-01 2011-10-12
PR-124 abd TY-02 2011-10-12
PR-124 abd TY-01 2011-10-13
PR-124 abd TY-02 2011-10-13
PR-124 abd TY-01 2011-10-18
PR-124 abd TY-02 2011-10-18

The results should be:
PR-123 abc TY-01 2011-10-16
PR-123 abc TY-02 2011-10-16
PR-124 abd TY-01 2011-10-18
PR-124 abd TY-02 2011-10-18

How will I do this? Thank you!

[aneeka]

I have this code but it results the first records for each items, what wrong with this.

select * from tablename where date=
(
select top 1 date from  tablename)
order by date desc

[urtrivedi]

I assume 2 thing that
1) product code is not entered more than once for same date
2) date column is not having time portion

select product_code,product_name,item_code, max(date) last_date  from tablename group by product_code,product_name,item_code

[adam_k]

I'll stick with my original query, provided that your data are as the ones you are showing as sample and don't contain time.
If they do then you need to adjust your sample data or let us know and I'll change the query accordingly.

[BitBlt]

Try this:

select a.product_code, a.productname, a.item_code, a.[date] 
from dbo.myTestProductTable a
inner join 
    (
    select product_code, max(date) as maxDate 
    from dbo.myTestProductTable 
    group by product_code
    ) b
on a.product_code = b.product_code
and a.[Date] = b.maxDate

Caveat: my test table defined the date column as a varchar...so if you need to do anything special with the date, you'll need to adjust the way it's handled.

Caveat2: Of course you'll need to put in an "order by" to sort your data.

Here are my table definition and test data inserts:

create table dbo.myTestProductTable
(
product_code varchar(10),
productname varchar(10),
item_code varchar(10),
[date] varchar(10)
)
go

insert into dbo.myTestProductTable
(product_code, productname, item_code, [date])
values
('PR-123', 'abc', 'TY-01', '2011-10-11'),
('PR-123', 'abc', 'TY-02', '2011-10-11'),
('PR-123', 'abc', 'TY-01', '2011-10-13'),
('PR-123', 'abc', 'TY-02', '2011-10-13'),
('PR-123', 'abc', 'TY-01', '2011-10-16'),
('PR-123', 'abc', 'TY-02', '2011-10-16'),
('PR-124', 'abd', 'TY-01', '2011-10-12'),
('PR-124', 'abd', 'TY-02', '2011-10-12'),
('PR-124', 'abd', 'TY-01', '2011-10-13'),
('PR-124', 'abd', 'TY-02', '2011-10-13'),
('PR-124', 'abd', 'TY-01', '2011-10-18'),
('PR-124', 'abd', 'TY-02', '2011-10-18')

(note: insert only works on SQL2008 because you can't use the multiple values on SQL2000)

Tested on SQL2000 sp4 and SQL2008 sp3. Hope this works for you! Good luck!