asked on

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

Rajkumar Gs

What is the datatype of the column 'ClientPrice' ?

Raj

Rajkumar Gs

If it is of some String datatype, sorting could have problem.

Raj

Rajkumar Gs

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

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

njgroup

ASKER

yes, you are right, its datatype was varchar, I changed it to string so its working fine

thanks a lot :)

Rajkumar Gs

Glad to help :)

Happy coding...
Raj