Select Random Rows

[mulhearn22]

Hi,

I have a table of products. Each product has a primary key (ID) and a foreign key (SubcategoryID). I want to randomly select 4 rows where SubcategoryID equals a given parameter.

How do I do this?

TIA,
Matt

[riteshjain1982]

If row repeatation is not an issue than you an use

SELECT TOP 4 * FROM Product WHERE SubcategoryID = Value

[GaryMazzone]

Try this:

SELECT TOP 4 * FROM Product WHERE SubcategoryID = Value
ORDER BY NEWID()

So what actually happens is that newid() returns a new GUID/unique identifier for every row in the result set, it then sorts based on the GUID value and then eventually selects the 4 top most rows.

[mulhearn22]

But that just returns the same top 4 every time. is there a way to pick a random 4?

[GaryMazzone]

Must have crossed post #3

[mulhearn22]

o I didn't see that. I thought it was part of your sig.

Sweet, I'll give it a try!

TY

[mulhearn22]

gary,

I'm getting an error on Value. It says it's an invalid column.

[mulhearn22]

i'm sorry. brain fart. I got it. Thanks!!

[GaryMazzone]

If this resolves this issue could you please mark this thread as RESOLVED using the Thread Tools above. This will let anyone else looking for a similar issue that you have recieved a good answer to this question.