Grouping Confusion - 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)

Grouping Confusion

Author

Topic

bananafishbone
Starting Member

7 Posts

Posted - 2005-08-23 : 04:23:42

Here's a sample of the table I'm working on.........


ID     PART	LINE	RATE	DTTM
1	55	WW3	2500	01/01/1999
2	55	WW3	2200	01/01/2000
3	55	WW3	2150	01/01/2001
4	55	WW6	900	01/01/1999
5	55	WW6	1000	01/01/2000
6	55	WW6	1500	01/01/2001
7	55	WW7	2000	01/01/1999
8	55	WW7	2350	01/01/2000
9	55	WW7	1750	01/01/2001

I want to query for the most recent rate for each part and line (actual table contains 300 different parts for each line).

i.e. the query would give the following table


ID	PART	LINE	RATE	DTTM
3	55	WW3	2150	01/01/2001
6	55	WW6	1500	01/01/2001
9	55	WW7	1750	01/01/2001

I'm getting confused with grouping I think.....

Could someone explain why this returns all rows, rather than the above?

SELECT MAX(dttm) AS DTTM, LINE, PART, RATE
FROM  TableName
GROUP BY LINE, PART, RATE

Thanks in advance for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 04:41:18

Try this

Select * from yourTable where DTTM in
(select max(DTTM) from yourTable group by Part, Line)

Madhivanan

Failing to plan is Planning to fail

bananafishbone
Starting Member

7 Posts

Posted - 2005-08-23 : 05:52:07

Seemed to work on the sample table, but doesn't on the 'real' data. Apologies for the long post, but here's part of the actual table


	ID      LINE    PART            RATE    DTTM
        1	00601	01-101-801	500	19/12/96
	3	00601	01-103-501	500	19/12/96
	5	00601	08-9101-551	500	19/12/96
	7	08301	01-171-55EU0	375	19/03/03
	9	08301	01-171-55EU4	375	24/03/03
	11	08301	01-171-55EU4	425	26/03/04
	13	08301	01-171-64EU0	375	19/03/03
	15	08301	01-171-64EU4	375	24/03/03
	17	08301	01-171-64EU4	425	26/03/04
	19	08301	01-171-73EU0	375	19/03/03
	21	08301	01-171-73EU1	375	19/03/03
	23	08301	01-171-73EU4	375	24/03/03
	25	08301	01-171-73EU4	375	26/03/03
	27	08301	01-171-73EU4	425	26/03/04
	29	08301	01-201-80W2211	500	26/10/98
	31	08301	01-201-80W2211	600	12/08/99
	33	08301	01-213-400	400	05/02/01
	35	08301	01-271-55EU0	375	19/03/03
	37	08301	01-271-55EU4	375	19/03/03
	39	08301	01-271-55EU4	375	26/03/03
	41	08301	01-271-55EU4	425	26/03/04
	43	08301	01-271-55EU4	400	25/08/04
	45	08301	01-271-64EU0	375	19/03/03
	47	08301	01-271-64EU0	425	26/03/04
	49	08301	01-271-64EU1	375	19/03/03
	51	08301	01-271-64EU4	375	19/03/03
	53	08301	01-271-64EU4	375	26/03/03
	55	08301	01-271-64EU4	425	26/03/04
	57	08301	01-271-64EU4	400	25/08/04
	59	08301	01-271-73EU0	375	19/03/03

Using the query as suggested


SELECT * FROM Line_rates 
WHERE(dttm IN
(SELECT MAX(dttm)FROM line_rates 
GROUP BY Part, Line))

results in the following


        ID      LINE    PART            RATE    DTTM
	1	00601	01-101-801	500	19/12/96
	3	00601	01-103-501	500	19/12/96
	5	00601	08-9101-551	500	19/12/96
	7	08301	01-171-55EU0	375	19/03/03
	11	08301	01-171-55EU4	425	26/03/04
	13	08301	01-171-64EU0	375	19/03/03
	17	08301	01-171-64EU4	425	26/03/04
	19	08301	01-171-73EU0	375	19/03/03
	21	08301	01-171-73EU1	375	19/03/03
	25	08301	01-171-73EU4	375	26/03/03
	27	08301	01-171-73EU4	425	26/03/04
	29	08301	01-201-80W2211	500	26/10/98
	31	08301	01-201-80W2211	600	12/08/99
	33	08301	01-213-400	400	05/02/01
	35	08301	01-271-55EU0	375	19/03/03
	37	08301	01-271-55EU4	375	19/03/03
	39	08301	01-271-55EU4	375	26/03/03
	41	08301	01-271-55EU4	425	26/03/04
	43	08301	01-271-55EU4	400	25/08/04
	45	08301	01-271-64EU0	375	19/03/03
	47	08301	01-271-64EU0	425	26/03/04
	49	08301	01-271-64EU1	375	19/03/03
	51	08301	01-271-64EU4	375	19/03/03
	53	08301	01-271-64EU4	375	26/03/03
	55	08301	01-271-64EU4	425	26/03/04
	57	08301	01-271-64EU4	400	25/08/04
	59	08301	01-271-73EU0	375	19/03/03

Comparing the tables, the query has correctly ommitted ID 9 and ID 15 for example, but look at ID 23,25 & 27. I expected only ID 27 (i.e. MAX(dttm) of that particular group).

Hope I'm making sense here?!

Thanks

Colin

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-23 : 05:58:14

I think you need something like this:

SELECT * 
FROM dbo.yourTable AS yt
JOIN (
		SELECT 
			MAX(yt.DTTM) AS DTTM, 
			yt.PART, 
			yt.LINE 
		FROM 
			dbo.yourTable AS yt
		GROUP BY 
			yt.PART, 
			yt.LINE) AS mx 
	ON mx.PART = yt.PART 
		AND mx.LINE = yt.LINE 
		AND mx.DTTM = yt.DTTM

The previous query will give you any row where DTTM matches the max of any part and line combination.

Mark

bananafishbone
Starting Member

7 Posts

Posted - 2005-08-23 : 06:11:53

Thanks Mark

That worked a treat.

I've a big pile of saw dust next to my desk as I've been scratching my head for two days over that!

Cheers

Colin

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-23 : 06:18:43

No problem!

Mark

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-24 : 08:40:59

You can try this:

Select * from yourTable t1 where t1.DTTM =
(select max(DTTM) from yourTable t2 where t2.Part = t1.part and t2.Line = t1.line)

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources