SQL: Select latest date for each year

[Fuma]

I have a table that keeps records of the number of items. So every year the data gets updated at least once. So I want an sql statement that will display the data for only the latest for each year.

ID------ItemCode----QTY-----Date
[COLOR="blue"]1--------A1------------20-------2000-03-02
2--------A1------------25-------2000-10-09[/COLOR]
[COLOR="green"]3--------A1------------30-------2001-03-07
4--------A1------------25-------2001-10-09[/COLOR]
5--------A1------------50-------2002-03-07
6--------A1------------65-------2003-03-07
7--------A2------------12-------2004-03-07
[COLOR="blue"]8--------A2------------12-------2005-03-07
9--------A2------------40-------2005-10-09[/COLOR]

I tried using sub queries, views to compare the data ,Max (date), group by and stuff but can't seem to get it. Any guidelines?

 

[Other Pineapple Smurf]

What SQL client / server are you using?

PostgreSQL has the command "DISTINCT ON" that can achieve what you want.


Psudocode:

SELECT DISTINCT ON (date_part_truncate (Date,"Year")) * from Table order by Date DESC;

-- this will select the last occurrence of each row by "year", to select the first occurance you will use ASC in your order by. But let me work out a subselect for you.

 

[Fuma]

What SQL client / server are you using?

PostgreSQL has the command "DISTINCT ON" that can achieve what you want.


Psudocode:

SELECT DISTINCT ON (date_part_truncate (Date,"Year")) * from Table order by Date DESC;

-- this will select the last occurrence of each row by "year", to select the first occurance you will use ASC in your order by. But let me work out a subselect for you.

SQl Server 2005

 

[Bighit]

use a cursor to select distinct item code and year.while looping in the cursor,do a select top query to pull out the latest quantity ordered by date.then insert this latest qty with item code and other data what you want into a temp table.outside cursor,do a select from temptable and there u go

 

[greggpb]

This is untested code but it should work, no cursors or temp tables or anything funny.. just a simple query.. it could still be optimised

Select A.Itemcode, A.date,B.Y, A.Qty
FROM
(Select ItemCode, Date,Qty From MYTABLE ) A INNER Join(

Select Itemcode, YEAR(DATE) y, MAX(Date) md
from MYTABLE
group by ItemCode, Year(DAte)
) B on a.Itemcode = B.Itemcode and a.Date = b.MD

 

[iblade]

Tested and working.

with Test ([DateValue]) as (
SELECT MAX(EntryDate) FROM [utCounter] GROUP BY YEAR(EntryDate)
)
SELECT [StockCount], [Id], [EntryDate] FROM [utCounter] WHERE [EntryDate] IN (
SELECT [DateValue] FROM Test)

 

[Other Pineapple Smurf]

Nice SQL greggpb - better than what I was going to suggest.

 

[greggpb]

Tested and working.

with Test ([DateValue]) as (
SELECT MAX(EntryDate) FROM [utCounter] GROUP BY YEAR(EntryDate)
)
SELECT [StockCount], [Id], [EntryDate] FROM [utCounter] WHERE [EntryDate] IN (
SELECT [DateValue] FROM Test)

Mines probally a bit faster due to the fact I i do my table scans and sort on a much smaller subset of data.. and my dad is bigger than your dad

 

[Fuma]

This is untested code but it should work, no cursors or temp tables or anything funny.. just a simple query.. it could still be optimised

Select A.Itemcode, A.date,B.Y, A.Qty
FROM
(Select ItemCode, Date,Qty From MYTABLE ) A INNER Join(

Select Itemcode, YEAR(DATE) y, MAX(Date) md
from MYTABLE
group by ItemCode, Year(DAte)
) B on a.Itemcode = B.Itemcode and a.Date = b.MD

Perfect. And it is super fast. Thanks greggpb
I sometimes hate sql stuff.