Tricky SELECT query question - 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 2000 Forums

Transact-SQL (2000)

Tricky SELECT query question

Author

Topic

y0n_1p
Starting Member

3 Posts

Posted - 2007-04-19 : 06:41:24

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

30421 Posts

Posted - 2007-04-19 : 06:46:10

It is not that tricky if you have paid attention in class.

You have to use MAX and INNER JOIN.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 07:01:14

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

y0n_1p
Starting Member

3 Posts

Posted - 2007-04-19 : 08:18:14

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-19 : 08:40:14

[code]-- 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[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 08:59:22

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-19 : 09:14:07

Peter,

SELECT * FROM ...

is bad practice, don't you know that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-19 : 10:55:00

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

y0n_1p
Starting Member

3 Posts

Posted - 2007-04-19 : 11:55:00

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.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-19 : 12:04:57

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources