This is probably simple - 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

SQL Server Development (2000)

This is probably simple

Author

Topic

JarWithLid
Starting Member

10 Posts

Posted - 2008-02-01 : 20:56:14

image_sets
==================
id - int
name - varchar
description - text
cdate

images
==================
id
name
filepath
description
cdate

set_image_j
==================
set_id
image_id

----------------------------------------------------
Sets are containers for images.

Need to get all columns for each image set, with the filepath for the newest image in each image set. So, I can do this:


SELECT 		s.id, s.name, s.cdate,
			(	SELECT		TOP 1 filepath
				FROM		images i
				INNER JOIN	set_image_j j
						ON	j.image_id = i.id
				WHERE		j.set_id = s.id
				ORDER BY	cdate DESC
			) AS newest_img
FROM 		image_sets s
ORDER BY 	s.name

But, now, what if I need all of the columns for the newest image? I can't figure that out.

Thanks,
Adam

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-02-01 : 22:41:35

instead of the top 1 correlated subquery, first get the resultset for "newest image in each image set"


select	j.set_id,
	max(i.cdate) cdate
from	Images i
inner
join	set_image_j j on
	i.id = j.image_id
group
by	j.set_id

then join to that from your Images table to "get all columns for each image set"

Nathan Skerl

JarWithLid
Starting Member

10 Posts

Posted - 2008-02-02 : 23:25:33

Ahhhhh.... awesome! Thanks! Like this, right?


SELECT		s.*, i.*
FROM		images i
INNER JOIN	set_image_j j 
		ON	j.image_id = i.id
INNER JOIN	image_sets s
		ON	s.id = j.set_id
INNER JOIN	(
				SELECT		j.set_id, max(i.cdate) as cdate
				FROM		images i
				INNER JOIN	set_image_j j 
						ON	j.image_id = i.id
				GROUP BY	j.set_id
			) newest
		ON	j.set_id = newest.set_id
		AND i.cdate = newest.cdate

JarWithLid
Starting Member

10 Posts

Posted - 2008-02-02 : 23:33:46

Wait, that kinda poses a problem... cdate is not necessarily a unique column. So...

ON j.set_id = newest.set_id
AND i.cdate = newest.cdate

...could match more than one row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 00:39:33

quote:
Originally posted by JarWithLid

Wait, that kinda poses a problem... cdate is not necessarily a unique column. So...

ON j.set_id = newest.set_id
AND i.cdate = newest.cdate

...could match more than one row.

You should then analyse your requirement i.e whether you want all the records or any one particular record in cases where there are more than one records existing for the most recent date.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-02-03 : 16:36:02

yea, that query requires a unique constraint on cDate + SetID. As visakh16 mentioned, youll need to define the biz reqs around choosing which SetID to use in the event > 1 is returned.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources