MS SQL

Sql Sort Query....

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2008

Posts: 2

Reputation: tytyguy is an unknown quantity at this point

Solved Threads: 0

tytyguy

Offline

Newbie Poster

Sql Sort Query....

May 4th, 2008

Ok, I have a e-commerce store that uses stored procedures to sort products. I have figured out how to sort my name and price but am having trouble sorting by rating bc the rating table doesnt have all the productids (only ones that have a rating) and also the rating table is in a different table than the products table so when I use the code below it only shows products that have a rating. Heres an example http://www.tunerplaza.com/c-304-carburetion.aspx?

Heres the sql code For the rating....

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ELSE IF @sortEntityName = 5 BEGIN --Sort By Rating
 
        INSERT #RowNumber (rating) 
        SELECT RT.Productid 
        FROM Rating RT 
        GROUP BY RT.ProductID
        ORDER BY MIN(RT.Rating)
 
        INSERT #displayorder select rating, displayorder from #RowNumber
END
ELSE IF @sortEntityName = 5 BEGIN --Sort By Rating

        INSERT #RowNumber (rating) 
        SELECT RT.Productid 
        FROM Rating RT 
        GROUP BY RT.ProductID
        ORDER BY MIN(RT.Rating)

        INSERT #displayorder select rating, displayorder from #RowNumber
END

Last edited by tytyguy; May 4th, 2008 at 7:26 pm.

•

Join Date: Dec 2004

Posts: 440

Reputation: Sulley's Boo will become famous soon enough

Solved Threads: 10

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

Re: Sql Sort Query....

May 5th, 2008

I tried the link ..
Sorting feature is working fine ..

Last edited by Sulley's Boo; May 5th, 2008 at 7:18 am.

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: Sql Sort Query....

May 9th, 2008

I think you want something like this

Select distinct pt.ProductID
from Products pt
left join Rating RT
on pt.productid = rt.productid
order by IsNull(Min(rt.Rating),0)

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: Sql Sort Query....

May 9th, 2008

ignore that last one and try this instead

Select pt.ProductID
from Products pt
left join Rating RT
on pt.productid = rt.productid
group by pt.productid
order by IsNull(Min(rt.Rating),0)