March 26, 2009 at 11:21 pm
I am working trying to get a SQL statement to slightly alter the order of the output of a Query. What I want to do is display the Home City first in DESC order then show the rest of the rows in DESC order. That in itself doesn't seem like it should be that hard, except I have one slight variance in that at the end of the list the owner wants Location records that start with RM to display at the bottom.
So basically here should be the output
Hometown Location - Asking Price
Out of Town Location sorted alphabetically - Asking Price
Properties beginning with RM - Asking Price at the end.
So all properties will be sorted by 2 items in a DESC fashion based on Location and Price
Here is my statement
Select * from tbl_Listings
where Location='Hometown'
Order by Location DESC, Price DESC
union all
select * from tbl_Listings
where Location<>'Hometown'
and Location not like 'RM%'
Order by Location DESC, Price DESC
union all
select * from tbl_Listings
where Location like 'RM%'
Order by Location DESC, Price DESC
So when I run this this the listings with RM in them appear alphabetically in DESC order in middle of the list instead of at the End
So is it possible to do this query? and if so how can I corrected the Output to show Hometown properties first, then all the other properties followed by the RM properties.
Thanks for all help on this.
I have been searching for 2 days on this, but being as I am relatively knew to using the SQL language I am not 100% positive I am searching for the right terms.
March 27, 2009 at 12:43 am
Hello,
Try ...
SELECT 1 AS SortKey, *
FROM tbl_Listings
WHERE Location='Hometown'
UNION ALL
SELECT 2 AS SortKey, *
FROM tbl_Listings
WHERE Location<>'Hometown'
AND Location not like 'RM%'
UNION ALL
SELECT 3 AS SortKey, *
FROM tbl_Listings
WHERE Location like 'RM%'
ORDER BY SortKey ASC, Location DESC, Price DESC
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 27, 2009 at 2:34 am
Is this what you're looking for?
-- make up some sample data
DROP TABLE #tbl_Listings
CREATE TABLE #tbl_Listings (Location VARCHAR(20), Price MONEY)
INSERT INTO #tbl_Listings (Location, Price)
SELECT 'RM10', 100000 UNION ALL
SELECT 'RM20', 200000 UNION ALL
SELECT 'RM30', 300000 UNION ALL
SELECT 'HometownA', 400000 UNION ALL
SELECT 'HometownB', 500000 UNION ALL
SELECT 'HometownC', 600000 UNION ALL
SELECT 'Awaytown1', 700000 UNION ALL
SELECT 'Awaytown2', 800000 UNION ALL
SELECT 'Awaytown3', 900000
-- run the query
SELECT *
FROM #tbl_Listings
ORDER BY
CASE WHEN Location = 'Hometown' THEN 1
WHEN Location LIKE 'RM%' THEN 3
ELSE 2
END, Location DESC, Price DESC
Results:
Location Price
-------------------- ---------------------
HometownC 600000.00
HometownB 500000.00
HometownA 400000.00
Awaytown3 900000.00
Awaytown2 800000.00
Awaytown1 700000.00
RM30 300000.00
RM20 200000.00
RM10 100000.00
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2009 at 8:54 am
Chris Morris (3/27/2009)
Is this what you're looking for?
-- make up some sample dataDROP TABLE #tbl_Listings
CREATE TABLE #tbl_Listings (Location VARCHAR(20), Price MONEY)
INSERT INTO #tbl_Listings (Location, Price)
SELECT 'RM10', 100000 UNION ALL
SELECT 'RM20', 200000 UNION ALL
SELECT 'RM30', 300000 UNION ALL
SELECT 'HometownA', 400000 UNION ALL
SELECT 'HometownB', 500000 UNION ALL
SELECT 'HometownC', 600000 UNION ALL
SELECT 'Awaytown1', 700000 UNION ALL
SELECT 'Awaytown2', 800000 UNION ALL
SELECT 'Awaytown3', 900000
-- run the query
SELECT *
FROM #tbl_Listings
ORDER BY
CASE WHEN Location = 'Hometown' THEN 1
WHEN Location LIKE 'RM%' THEN 3
ELSE 2
END, Location DESC, Price DESC
Results:
Location Price-------------------- ---------------------
HometownC 600000.00
HometownB 500000.00
HometownA 400000.00
Awaytown3 900000.00
Awaytown2 800000.00
Awaytown1 700000.00
RM30 300000.00
RM20 200000.00
RM10 100000.00
Cheers
ChrisM
This worked awesome. I ran this on my SQL server and it was perfect. Thanks a bunch.
For my website I currently have it backending to an access DB and I just found a few difference in the command structure between SQL and access.
In access when I run this sql query against the #tbl_listings it throughs an error "Syntax error (missing operator) in query expression 'Case WHEN Location = ....
Thanks for the very quick response.:-D
March 27, 2009 at 10:19 am
You're welcome Blanktree, thanks for the feedback.
If you don't already know, TSQL has two flavours of CASE, simple and searched. The query above uses the searched flavour because the conditions compare different attributes. I'd guess that Access would be happy with the simple CASE:
CASE mycolumn
WHEN somevalue THEN ...
WHEN someothervalue THEN ...
as opposed to
CASE WHEN someexpression THEN ...
WHEN someotherexpression THEN ...
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2009 at 1:14 pm
Chris Morris (3/27/2009)
You're welcome Blanktree, thanks for the feedback.If you don't already know, TSQL has two flavors of CASE, simple and searched. The query above uses the searched flavor because the conditions compare different attributes. I'd guess that Access would be happy with the simple CASE:
CASE mycolumn
WHEN somevalue THEN ...
WHEN someothervalue THEN ...
as opposed to
CASE WHEN someexpression THEN ...
WHEN someotherexpression THEN ...
Cheers
ChrisM
I found a few variables here making this even more an abnomally.
When I run the same query in Access/SQL I get the Results I need, In my case I am putting the SQL command through DreamWeaver while editing the page and I get a few differences in the commands.
Dreamweaver seems to take some similarities of SQL server sql but then also doesn't like some commands specific to Access
I found a slight work around to the Case statement by using IIF
For example in Access sql
IIF(Location = 'HometownA', 1,IIF(Location LIKE 'RM*',3,2)), in Access returns perfectly, now if I put the same code in DreamWeaver through the SQL window I have to make the command read
IIF(Location = 'HometownA', 1,IIF(Location LIKE 'RM%',3,2)), then it displays perfectly on the Web Page. So I guess Access doesn't like the % character natively so I have to use the * wildcard, so to get the page to display in DreamWeaver I have to use the %
So all in all this was an excellent learning opportunity as I was doing some test development through SQL Server on this page, when the Production page is actually Access. I wasn't aware of the differences in the SQL language supported by Access, so in Test using SQL Server things worked perfectly, but in Production I got weird errors etc.
So in the end to make this work properly in Production I used this
SELECT *
FROM tbl_Listings
ORDER BY IIF(Location = 'HometownA', 1,IIF(Location LIKE 'RM%',3,2)),
Location DESC, Price DESC
Now I know of more reasons to move the Prod WebSite to SQL Server and not run into these abnomolies 🙂
I thank you once again for your help on this as I had surfed all over the internet and then didn't realize I was fighting against a few abnomolies.
Have a great weekend
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply