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 2000 Forums
 Transact-SQL (2000)
 Tricky SELECT query question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

y0n_1p
Starting Member

3 Posts

Posted - 04/19/2007 :  06:41:24  Show Profile  Reply with Quote
I have two tables, a master and details table, and I want to create a query to return a set of records containing unique itemcodes with their latest costs. The tables are actually delivery tables with some of the following fields:

MASTER TABLE
ID
ReferenceNo
TransactionDate

DETAILS TABLE
ID
ReferenceNo
ItemCode
ItemCost

The two tables are linked by the reference number field.

How do I select a unique list of ALL the itemcodes showing ONLY their latest costs? The items have different delivery dates and some of them are delivered several times with costs possibly changing on each delivery.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/19/2007 :  06:46:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It is not that tricky if you have paid attention in class.

You have to use MAX and INNER JOIN.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/19/2007 :  07:01:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or correlated subquery
SELECT	d.*
FROM	DetailsTable AS d
WHERE	d.ReferenceNo = (SELECT TOP 1 m.ReferenceNo FROM MasterTable AS m WHERE m.ReferenceNo = d.ReferenceNo ORDER BY m.TransactionDate DESC)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

y0n_1p
Starting Member

3 Posts

Posted - 04/19/2007 :  08:18:14  Show Profile  Reply with Quote
Thank you very much for your rather polite answer. But, if you have paid attention to my question, you might have given me a working solution.

To better understand my question, here is an example:

MASTERTABLE

ID ReferenceNo TransactionDate
1 ABC-123 01/01/2007
2 ABC-456 01/02/2007
3 ABC-789 01/03/2007



DETAILSTABLE

ID ReferenceNo ItemCode ItemCost
1 ABC-123 00001 10.00
2 ABC-123 00002 12.00
3 ABC-456 00001 11.00
4 ABC-456 00003 15.00
5 ABC-456 00004 14.00
6 ABC-789 00001 9.00
7 ABC-789 00004 15.00
8 ABC-789 00005 8.00


This is the resultset I need:

ItemCode ItemCost
00001 9.00
00002 12.00
00003 15.00
00004 15.00
00005 8.00


Again, thank you in advance.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/19/2007 :  08:40:14  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
-- prepare sample data
declare @master table
(
	ID int,
	referenceno varchar(20),
	TransactionDate datetime
)

declare @details table
(
	ID int,
	ReferenceNo varchar(20),
	ItemCode varchar(10),
	ItemCost numeric(25,2)
)

insert @master
select 1, 'ABC-123', '01/01/2007' union all
select 2, 'ABC-456', '01/02/2007' union all
select 3, 'ABC-789', '01/03/2007'

insert @details
select 1, 'ABC-123', '00001', 10.00 union all
select 2, 'ABC-123', '00002', 12.00 union all
select 3, 'ABC-456', '00001', 11.00 union all
select 4, 'ABC-456', '00003', 15.00 union all
select 5, 'ABC-456', '00004', 14.00 union all
select 6, 'ABC-789', '00001', 9.00 union all
select 7, 'ABC-789', '00004', 15.00 union all
select 8, 'ABC-789', '00005', 8.00

-- final query
select t1.ItemCode, t1.ItemCost
from @details t1 join @master t2 on t1.referenceno = t2.referenceno
Join
(
	select d.ItemCode, max(TransactionDate) as TransactionDate
	from @details d join @master m
	on d.referenceno = m.referenceno
	group by d.ItemCode
) t3
on t1.ItemCode= t3.ItemCode and 
t2.TransactionDate = t3.TransactionDate
order by 1


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/19/2007 :  08:59:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Shorter, based on clearer specifications...
SELECT		d.*
FROM		@Details AS d
WHERE		d.ID = (SELECT TOP 1 q.ID FROM @Master AS m INNER JOIN @Details AS q ON q.ReferenceNo = m.ReferenceNo WHERE q.ItemCode = d.ItemCode ORDER BY m.TransactionDate DESC)
ORDER BY	d.ItemCode


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/19/2007 :  09:14:07  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Peter,

SELECT * FROM ...

is bad practice, don't you know that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 04/19/2007 :  10:55:00  Show Profile  Reply with Quote
quote:
But, if you have paid attention to my question, you might have given me a working solution.



This makes me want to type out every swear word I know.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

y0n_1p
Starting Member

3 Posts

Posted - 04/19/2007 :  11:55:00  Show Profile  Reply with Quote
My deepest thanks to both Harsh and Peter. Both solutions give me the resultset I needed.



quote:
Originally posted by DonAtWork

quote:
But, if you have paid attention to my question, you might have given me a working solution.



This makes me want to type out every swear word I know.


I'm so sorry DonAtWork. I wasn't aware that new forum members must keep mum when insulted. You may insult me too if you want.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 04/19/2007 :  12:04:57  Show Profile  Reply with Quote
You asked for these folks to do your work for you, not explain how it works, or to help you understand. Then you get insulting when they attempt to HELP you instead of DOING YOUR WORK FOR YOU. Now do you understand?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
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.09 seconds. Powered By: Snitz Forums 2000