Random record - 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 2005 Forums

Transact-SQL (2005)

Random record

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2007-08-06 : 06:03:43

I'm sure this is simple but I can't for the life of me get it working.

I have a table of categories and a table of photos (the table of photos contains CategoryId). What I would like to do is randomly select a photo and it's details (i.e. mulitple columsn) from photos to represent the category so a nested select won't work and because it has to be joined on the CategoryId I can't seem to get a join to work either.

The tables in their simplest form are:

Category:
CategoryId
CategoryName

Photo:
PhotoId
PhotoName
Width
Height

Ideally I want to avoid selecting the PhotoId and then re-joining to get the details as this is already part of a larger select but atm that's the only solution I can see.

TIA

Tim

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-06 : 07:36:37

For any given @CategoryID... something like this...

SELECT TOP 1 p.*
FROM Category c,
Photo p
WHERE p.CategoryID = c.CategoryID
AND c.CategoryID = @CategoryID
ORDER BY NEWID()

--Jeff Moden

timgaunt
Posting Yak Master

115 Posts

Posted - 2007-08-06 : 07:49:50

Thanks but I don't think that'll work, I apologise as it's my explaination, this is for a category listing page so all Categories need to be selected and a single Photo from that category is what I need which is why I've got stuck :)

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 13:24:04

Here is a sampel of something you can try. I left out some of the column so I could demonstrate the fundimental concept:

DECLARE @Photo TABLE
(
	PhotoID INT,
	CategoryID INT
)

INSERT @Photo
SELECT 1, 1
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 1
UNION ALL SELECT 5, 2
UNION ALL SELECT 6, 2
UNION ALL SELECT 7, 2
UNION ALL SELECT 8, 2
UNION ALL SELECT 9, 3
UNION ALL SELECT 10, 3
UNION ALL SELECT 11, 3
UNION ALL SELECT 12, 3
UNION ALL SELECT 13, 3
UNION ALL SELECT 14, 3

SELECT 
	*
	FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY NEWID()) AS RowNumber
		FROM
			@Photo
	) AS T
WHERE
	T.RowNumber = 1

-Ryan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-07 : 02:39:34

As you want to return one row for each categoryid, you can use this also

select * from @photo T
where photoid=(select top 1 photoid from @photo where categoryid=T.categoryid)

Madhivanan

Failing to plan is Planning to fail

timgaunt
Posting Yak Master

115 Posts

Posted - 2007-08-07 : 04:46:08

Fantastic Ryan thanks, that's right on the money.

I also tried yours Madhivanan however the performance wasn't great though it did produce the desired resultset.

Tim

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-07 : 06:26:38

quote:
Originally posted by timgaunt

Fantastic Ryan thanks, that's right on the money.

I also tried yours Madhivanan however the performance wasn't great though it did produce the desired resultset.

Tim

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Yes it is. If you dont use SQL Server 2005, then you can use that method

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources