Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-25
6
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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