Retrieve featured products - 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)

Retrieve featured products

Author

Topic

mima
Starting Member

4 Posts

Posted - 2009-08-10 : 09:19:45

HI to all.
I‘m working on ecommerce platform and needs to retrieve featured products that will be displayed on front end home page.

All products are stored in products table and have one column called ‘Featured’ with data type (bit). If this is set to true(1) this means that this is featured product and should be displayed on home page.

Now I need SQL query that will return for example 20 products, but each time when executed random 20 products, whose are marked as featured (dbo.Products.Featured = true) and in case when there is not 20 of featured to take any other (by random of course) to get 20.

Hope I was clear in explanation.

Any help is appreciated!!!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-10 : 09:55:39

[code]SELECT TOP 20 *
FROM Products
ORDER BY
CASE WHEN Featured = 1 THEN 'A' ELSE 'B' END
,NEWID()[/code]

mima
Starting Member

4 Posts

Posted - 2009-08-10 : 12:30:56

working like a charm, thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-10 : 12:37:37

Make sure you Index the column Featured to include only the columns you need.( So for the example below you would want to INCLUDE the following columns with your index (MyProdID,MyProdName)


SELECT TOP 20 MyProdID,MyProdName
FROM Products
where Featured = 1
ORDER BY NEWID()

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources