CODEWITHSUNDEEP
sql-serverstored-procedures
user1603179
user1603179

Reputation: 87

SQL Server order by query

I have two tables called Listing and ListingProperties

Listing (ID, CurrentPrice)          
ListingProperties (id, listingId, Fixedprice)

The problem is I want to order all listings by Fixedprice. But some listings don't have a Fixedprice.

In that case I want to check current price and compare with others Fixedprice and then order.

Listing

id name currentprice
1  a      10
2  b       50
3  c       40

ListingProperties

id listingId  Fixedprice
1       1        20
2       3        30

after order the required order is

name
a
c
b

Upvotes: 0

Views: 61

Answers (2)

Yván Ecarri
Yván Ecarri

Reputation: 1738

Your question is not clear but I'll try to guess.

1) You have to join your tables:

SELECT <fields here>
FROM Listing L
LEFT JOIN ListingProperties LP
ON L.ID = LP.ListingId

This query assumes that you dont have REPEATED VALUES for a ListingId in ListingProperties. If you do have multiple values you have to specify priority criteria to decide which FixedPrice you want to show.

2) Once you have the joined query, you have to use the CASE statetemnt to select betwheen current and fixed. Tis assumes that you have AT LEAST ONE record in Listing for every item you want to list. If you do not have at least one, you'll have to do some forther tricks.

SELECT CASE WHEN L.CurrentPrice IS NULL THEN LP.FicedPrice ELSE L.CurrentPrice END 
FROM Listing L
LEFT JOIN ListingProperties LP
ON L.ID = LP.ListingId
ORDER BY CASE WHEN L.CurrentPrice IS NULL THEN LP.FicedPrice ELSE L.CurrentPrice END

Upvotes: 0

cjk
cjk

Reputation: 46475

Try this:

SELECT Name
FROM Listing l
LEFT JOIN ListingProperties lp ON l.id=lp.listingid
ORDER BY ISNULL(lp.FixedPrice, l.currentprice)

Upvotes: 1

Related Questions