best way to do this order - 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)

best way to do this order

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-12-15 : 07:11:05

i'm doing a query on my products table - now it just gives me a recordset of products in a specific category in a random order - -
my current query is:
SELECT products.id, catproduct.catid, products.title, category.categoryname, category.parentcatid FROM catproduct INNER JOIN category ON catproduct.catid = category.catid RIGHT OUTER JOIN products ON catproduct.productid = products.id left join p_items on products.id=p_items.productid WHERE p_items.display=1 and category.parentcatid = 196 and products.id!=856 order by newid()

now I want to order it by number of sales for each product sold and then randomly for the rest - what's the best way to do this

I have products with productid which links to p_items with products.productid=p_items.productid

then I have orders which orders.usrsessid=shoppingcart.usrsessid
and then shoppingcart has the item_id of what was ordered.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-15 : 07:18:57

esthera - 1156 Posts - Flowing Fount of Yak Knowledge...

You really should know:
We need table structure, sample data and wanted result in relation to the sample data because it is not easy to help without that information.

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-12-15 : 07:21:11

Don't tell lies Fred. You KNOW Peter or Madhi will read the OP's mind and come up with the right answer

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-15 : 07:24:16

Argh. Yes. How could I miss that I was a little absent-minded.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources