in sql 2005, why order by is not working with inner join?

hi,

I have this sql select from many tables by inner join, I wanna order the result by price descending, but its not working, here is my try:

select pl.LeamraCode, pl.EnName as Name, pl.ImgPath, pt.EnProductType, pp.ClientPrice, pp.ClientMaxDiscount as Discount, c.EnCurrencyName as Currency, sq.StockQTY
from ProductList pl inner join ProductType pt
on pl.ProductTypeId = pt.id
inner join StockQTY sq on pl.LeamraCode = sq.LeamraCode
inner join ProductPrice pp on pl.LeamraCode = pp.LeamraCode
inner join Currency c on pp.CurrencyId = c.id
order by pp.ClientPrice Desc


njgroupAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
If your answer is some String datatype, try this modified query (Casted)
SELECT  pp.ClientPrice ,
        pl.LeamraCode ,
        pl.EnName AS Name ,
        pl.ImgPath ,
        pt.EnProductType ,
        pp.ClientMaxDiscount AS Discount ,
        c.EnCurrencyName AS Currency ,
        sq.StockQTY
FROM    ProductList pl
        INNER JOIN ProductType pt ON pl.ProductTypeId = pt.id
        INNER JOIN StockQTY sq ON pl.LeamraCode = sq.LeamraCode
        INNER JOIN ProductPrice pp ON pl.LeamraCode = pp.LeamraCode
        INNER JOIN Currency c ON pp.CurrencyId = c.id
ORDER BY CAST(pp.ClientPrice AS DECIMAL(18,2)) DESC

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
What is the datatype of the column 'ClientPrice' ?

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
If it is of some String datatype, sorting could have problem.

Raj
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rajkumar GsSoftware EngineerCommented:
OR

Just to double check, try this modified query (just moved the ClientPrice Column to first)
SELECT  pp.ClientPrice ,
        pl.LeamraCode ,
        pl.EnName AS Name ,
        pl.ImgPath ,
        pt.EnProductType ,
        pp.ClientMaxDiscount AS Discount ,
        c.EnCurrencyName AS Currency ,
        sq.StockQTY
FROM    ProductList pl
        INNER JOIN ProductType pt ON pl.ProductTypeId = pt.id
        INNER JOIN StockQTY sq ON pl.LeamraCode = sq.LeamraCode
        INNER JOIN ProductPrice pp ON pl.LeamraCode = pp.LeamraCode
        INNER JOIN Currency c ON pp.CurrencyId = c.id
ORDER BY pp.ClientPrice DESC

Open in new window


Are you sure still it is sorting not properly (desc) ?

Raj
0
 
njgroupAuthor Commented:
yes, you are right, its datatype was varchar, I changed it to string so its working fine

thanks a lot :)
0
 
Rajkumar GsSoftware EngineerCommented:
Glad to help :)

Happy coding...
Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.