?
Solved

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

Posted on 2011-02-25
6
Medium Priority
?
275 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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