retrieve recent distinct data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

retrieve recent distinct data

Author

Topic

katerina
Starting Member

1 Post

Posted - 2007-06-13 : 09:06:08

Im new to this forum and i had a good look arount similar issues posted in the past. I have tried to retrieve the data with 'Max' and 'Having' clauses as on previous suggestions but had no luck. My script is as below:

select a.customer_code, b.part_no, c.category, b.price as price_per_unit,max(b.date_shipped)
from armaster a, shippers b, inv_master c
where a.customer_code = b.cust_code and a.ship_to_code = b.ship_to_no
and b.part_no = c.part_no and c.type_code = 'AF'
and year (b.date_shipped) = 2007 and b.price > 0
group by a.customer_code, a.addr_sort1, a.territory_code,
a.salesperson_code,b.part_no, c.[description], c.category, b.price, b.date_shipped
order by a.customer_code, b.part_no

The above retrieves the data i require however as you can see on a sample below due to price field not being the same it duplicates the part number:
ESH12P 1.75000000 2007-01-15 13:37:34.613
ESH12P 1.75206000 2007-05-16 21:30:46.993

i just want to see the very last sold price.

Can you advise?
Thank you

katerinas

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-13 : 09:41:59

quote:
Originally posted by katerina

Im new to this forum and i had a good look arount similar issues posted in the past. I have tried to retrieve the data with 'Max' and 'Having' clauses as on previous suggestions but had no luck. My script is as below:

select a.customer_code, b.part_no, c.category, b.price as price_per_unit,max(b.date_shipped)
from armaster a, shippers b, inv_master c
where a.customer_code = b.cust_code and a.ship_to_code = b.ship_to_no
and b.part_no = c.part_no and c.type_code = 'AF'
and year (b.date_shipped) = 2007 and b.price > 0
group by a.customer_code, a.addr_sort1, a.territory_code,
a.salesperson_code,b.part_no, c.[description], c.category, b.price, b.date_shipped
order by a.customer_code, b.part_no

The above retrieves the data i require however as you can see on a sample below due to price field not being the same it duplicates the part number:
ESH12P 1.75000000 2007-01-15 13:37:34.613
ESH12P 1.75206000 2007-05-16 21:30:46.993

i just want to see the very last sold price.

Can you advise?
Thank you

katerinas

One of you other fields in the group by is causing the duplicate. You can either (1)Eliminate the field from the group by and select or (2)use a derived table to get what you need ...

select PartNo,ShipDate,morecolumns...
from myTable a
inner join
(
select PartNo,max(ShipDate)
from mytable
group by PartNo
) as b on a.PartNo=b.PartNo and a.ShipDate=b.ShipDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources