SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 retrieve recent distinct data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

katerina
Starting Member

United Kingdom
1 Posts

Posted - 06/13/2007 :  09:06:08  Show Profile  Reply with Quote
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 - 06/13/2007 :  09:41:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000