Distinct Random Rows using NewID()

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)

Distinct Random Rows using NewID()

Author

Topic

PaoloTCS
Starting Member

4 Posts

Posted - 2008-05-26 : 08:32:57

I have 2 tables, Artists and Artworks.
I have a query:

SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()

This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,

Paolo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 08:39:41

[code]SELECT a.ArtistID,
a.FullName,
w.ArtworkName,
w.Image
FROM (
SELECT TOP 4 ArtistID,
FirstName + ' ' + LastName AS FullName,
FROM dbo.Artists
ORDER BY NEWID()
) AS a
INNER JOIN dbo.Artworks AS w ON w.ArtistID = a.ArtistID[/code]

If there are multiple artwork for the artists, you will need a different approach.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 08:43:36

This will work for all combinations of artist and artwork (only artists having at least one artwork).

SELECT TOP (4)	d.ArtistID
		d.FullName,
		d.ArtworkName,
		d.Image
FROM		(
			SELECT		a.ArtistID,
					a.FirstName + ' ' + a.LastName AS FullName,
					w.ArtworkName,
					w.Image,
					ROW_NUMBER() OVER (PARTITION BY a.ArtistID ORDER BY NEWID()) AS RecID
			FROM		dbo.Artists AS a
			INNER JOIN	dbo.Artworks AS w ON w.ArtistID = a.ArtistID
		) AS d
WHERE		d.RecID = 1
ORDER BY	NEWID()

E 12°55'05.25"
N 56°04'39.16"

PaoloTCS
Starting Member

4 Posts

Posted - 2008-05-26 : 09:20:15

Thank you very much for your prompt reply.
There are indeed multiple artworks per artist. I tried your second suggestion, but I get errors.
Error message:
Error in SELECT clause: expression near '.'.
Missing FROM clause.
The OVER SQL construct or statement is not supported."

I am using a remote server, so I am not sure if it is SQL 2000 or SQL 2005.

Thanks again,

Paolo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 09:24:46

It seems you are running SQL Server 2000.
Execute SELECT @@VERSION for confirmation.

E 12°55'05.25"
N 56°04'39.16"

PaoloTCS
Starting Member

4 Posts

Posted - 2008-05-26 : 09:41:34

Thanks. Just checked, and it seems that I am running sql2005 there.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

In running in query analyser, I get the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AS'.

When running your second suggestion...

Paolo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 09:48:31

quote:
Originally posted by PaoloTCS

Thanks. Just checked, and it seems that I am running sql2005 there.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

In running in query analyser, I get the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AS'.

When running your second suggestion...

Paolo

Make sure your db compatibility level set to 90. use sp_dbcmptlevel to check your dbs current compatibility level.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 09:51:24

quote:

Originally posted by Peso

This will work for all combinations of artist and artwork (only artists having at least one artwork).

SELECT TOP (4)	d.ArtistID,
		d.FullName,
		d.ArtworkName,
		d.Image
FROM		(
			SELECT		a.ArtistID,
					a.FirstName + ' ' + a.LastName AS FullName,
					w.ArtworkName,
					w.Image,
					ROW_NUMBER() OVER (PARTITION BY a.ArtistID ORDER BY NEWID()) AS RecID
			FROM		dbo.Artists AS a
			INNER JOIN	dbo.Artworks AS w ON w.ArtistID = a.ArtistID
		) AS d
WHERE		d.RecID = 1
ORDER BY	NEWID()

E 12°55'05.25"
N 56°04'39.16"

Missed a , there

PaoloTCS
Starting Member

4 Posts

Posted - 2008-05-26 : 10:08:45

visakh16: That did it, thanks very much to both of you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources