Solved

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

Posted on 2011-02-25
6
270 Views
Last Modified: 2013-12-17
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


0
Comment
Question by:njgroup
  • 5
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34986120
What is the datatype of the column 'ClientPrice' ?

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34986122
If it is of some String datatype, sorting could have problem.

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34986123
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 34986127
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
 

Author Comment

by:njgroup
ID: 34986652
yes, you are right, its datatype was varchar, I changed it to string so its working fine

thanks a lot :)
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34986662
Glad to help :)

Happy coding...
Raj
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now