Grouping / MAX / imbedded SQL problem

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 / MAX / imbedded SQL problem

Author

Topic

red_hot_robbie
Starting Member

2 Posts

Posted - 2005-03-30 : 05:51:25

I have a product table with three identifiers - the SQL auto-incrementing field ("pds_id"), a product number field ("pds_original_id"), and a version number field ("pds_amendment_no").

I need to get a recordset of the LATEST version of the distinct products - so if I have two different products with three versions of product #1 and two versions of product #2, I want a recordset that yields the following:

----------------------------------------------
1200 (pds_id), 1 (pds_original_id), 3 (pds_amendment_no)
1350 (pds_id), 2 (pds_original_id), 2 (pds_amendment_no)
----------------------------------------------

If I leave out "pds_id" its easy:

----------------------------------------------
SELECT
pds_original_id, max(pds_amendment_no)
FROM
tblPDS
GROUP BY
pds_original_id
----------------------------------------------

which will give me:
----------------------------------------------
1 (pds_original_id), 3 (pds_amendment_no)
2 (pds_original_id), 2 (pds_amendment_no)
----------------------------------------------

But I can't leave "pds_id" out!! So at the moment I have to use temporary tables:

----------------------------------------------
SELECT
pds_original_id, max(pds_amendment_no) as pds_amendment_no
INTO
#tmpLatestPDS
FROM
tblPDS
GROUP BY
pds_original_id

SELECT
pds_id, pds_prod_name
FROM
tblPDS pds
JOIN
#tmpLatestPDS tmp ON tmp.pds_original_id = pds.pds_original_id
AND tmp.pds_amendment_no = pds.pds_amendment_no

DROP TABLE #tmpLatestPDS
----------------------------------------------

Is this the only way? I hate structuring things like this.

Thanks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-30 : 08:30:07

You can use a derived table, like so:

SELECT
	pds.pds_id
	,pds.pds_prod_name
	,LatestPDS.pds_original_id
	,LatestPDS.pds_amendment_no
FROM
	tblPDS pds
	JOIN(	SELECT
			pds_original_id
			,MAX(pds_amendment_no) AS pds_amendment_no
		FROM
			tblPDS
		GROUP BY
			pds_original_id ) AS LatestPDS
ON
	pds.pds_original_id = LatestPDS.pds_original_id
	AND pds.pds_amendment_no = LatestPDS.pds_amendment_no

rockmoose

red_hot_robbie
Starting Member

2 Posts

Posted - 2005-03-30 : 09:32:23

Aha! Just what I was looking for.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources