Cant work out this SQL to return 4 random records

I've got a product table in SQL 2000 that contains say these rows:

ProductID int
ProductName varchar
IsSpecial bit

I want to always return 4 random specials from a query and can do this fine by using:

SELECT TOP 4 ProductID,ProductName
FROM Products WHERE IsSpecial = 1
ORDER BY NEWID()

This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.

What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?

Is this possible?

Thanks

Hello my friend,

The following SQL should work. If you have any questions or problems with it, please let me know.

Kind regards

Scotty

SELECT TOP 4 * FROM
(
SELECT TOP 4 ProductID, ProductName FROM Products WHERE IsSpecial = 1

UNION ALL

SELECT ProductID, ProductName FROM Products WHERE IsSpecial = 0

) myTable

ORDER BY IsSpecial DESC, NewID()

|||

Excellent Scotty, that does exactly what i was looking for!

Cheers.