sorting queries
Paul Sorrell
SELECT * FROM Items WHERE Availability = True ORDER By Category DESC,
Price DESC
These are my categories.........
SideBySide
Pair
Hammergun
UnderOver
DoubleRifle
Combination
Collectible
Auto
Rifle
Single
Airgun
My question is, can I sort the categories in any way other than ascending or
descending?
I want to order them as above.
Thanks in advance
Paul S
Bob Barrows
Enter your categories into the table and enter the apropriate rank numbers.
Then join Items to this new table using the Category columns and order by
the Rank column.
Bob Barrows
Tom B
that? If you gave me that list on a piece of paper and asked me to sort
them, I can guarantee that I would never have come up with that order.
Anyway, enough of my snotty comments.
I assume that your Categories are in a separate table, add a field for the
order that the category should be sorted by.
tblCategories
CategoryID int
Title varchar(50)
SortOrder int
Example data
CategoryID Title SortOrder
1 Pair 2
2 SideBySide 1
3 Hammergun 3
"Paul Sorrell" <egg...@eggunsNOSPAM.demon.co.uk> wrote in message
news:bfmrlo$47k$1$8300...@news.demon.co.uk...
Paul Sorrell
categories.
I understand the concept but need a quick and easy fix for what alreadt
exists.
Is there no way I can call one category after another without a separate
table.
Something like
select * from items where category = sidebyside then pair then hammergun
etc.....
--
Thank you,
Paul Sorrell
http://www.egguns.demon.co.uk
"Tom B" <shu...@hotmail.com> wrote in message
news:udG3ZzVU...@TK2MSFTNGP11.phx.gbl...
Mark Schupp
--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
msc...@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Paul Sorrell" <egg...@eggunsNOSPAM.demon.co.uk> wrote in message
news:bfn1cm$dju$1$8300...@news.demon.co.uk...
Chris Hohmann
> You have hit the nail on the head, I do not have a separate table for
> categories.
> I understand the concept but need a quick and easy fix for what
alreadt
> exists.
>
> Is there no way I can call one category after another without a
separate
> table.
>
> Something like
> select * from items where category = sidebyside then pair then
hammergun
> etc.....
> --
>
> Thank you,
> Paul Sorrell
> http://www.egguns.demon.co.uk
Assuming RDMS = SQL Server
SELECT
*
FROM
Items
ORDER BY
CASE Category
WHEN 'SideBySide' THEN 0
WHEN 'Pair' THEN 1
WHEN 'HammerGun' THEN 2
WHEN 'UnderOver' THEN 3
WHEN 'DoubleRifle' THEN 4
WHEN 'Combination' THEN 5
WHEN 'Collectible' THEN 6
WHEN 'Auto' THEN 7
WHEN 'Rifle' THEN 8
WHEN 'Single' THEN 9
WHEN 'Airgun' THEN 10
END
Bob Barrows
Don't do things quick and easy. Do them right!!
Tell me: what is so slow and hard about creating a 2-column table with,
what, 10 rows of data? If that takes you any longer than 10 minutes you
should be really ashamed of yourself! Add another 30 seconds to add a join
statement to your query, and you have a solution in less than 11 minutes!
This isn't quick and easy???
Why is this the right way to do it? Why not use the clever solution that
Chris provided?
1. Performance - while Chris's solution will work, it won't allow any
indexes to be used to sort your data, leading to poor query performance
2. Re-use - is this the only query that will need to use this sort order?
Are you sure? Do you want to reproduce that CASE statement every time you
need this sort order? And what about if you need to modify that sort order?
Are you going to remember every place you used that CASE statement? Is using
a separate ranking table starting to seem less slow and hard?
Bob Barrows
Paul Sorrell wrote:
> You have hit the nail on the head, I do not have a separate table for
> categories.
> I understand the concept but need a quick and easy fix for what
> alreadt exists.
>
> Is there no way I can call one category after another without a
> separate table.
>
> Something like
> select * from items where category = sidebyside then pair then
> hammergun etc.....
>
Tom B
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:u%23EZg8XU...@TK2MSFTNGP10.phx.gbl...
Bob Barrows
<snip>
I want to apologize for the tone of this reply (I stand by its content). I
regret any unintended slight to Chris who was merely attempting to answer
the question given. I will say again that his solution was, indeed, clever.
Bob
John Beschler
thought to myself, that won't work, so I tried it and it
does (as you all already know).
However, I'm stumped as to why. The ORDER BY Clause (as I
understand it) takes column names as parameters:
I.E. ORDER BY LastName
Or, it can use the position of the column in the SELECT
statement:
I.E. SELECT Lastname, Firstname FROM MYTable ORDER BY 1
Chris' statement doesn't seem to follow either of those
rules.
Can one of you SQL experts explain this to me?
Bob Barrows
column is determined by the output of the CASE statement. It's as if you put
the CASE statement into your SELECT clause like this:
SELECT
*,
CASE Category
WHEN 'SideBySide' THEN 0
WHEN 'Pair' THEN 1
WHEN 'HammerGun' THEN 2
WHEN 'UnderOver' THEN 3
WHEN 'DoubleRifle' THEN 4
WHEN 'Combination' THEN 5
WHEN 'Collectible' THEN 6
WHEN 'Auto' THEN 7
WHEN 'Rifle' THEN 8
WHEN 'Single' THEN 9
WHEN 'Airgun' THEN 10
END As Rank
FROM
Items
ORDER BY Rank
HTH,
Bob Barrows
John Beschler
>.
>
Chris Hohmann
was not advocating this method for general use. It was in response to "I
understand the concept but need a quick and easy fix for what
better) solution is that Paul may want to a string instead of numbers
for the rank column. A numeric column can bite you in the ass later on
when you need to move a record in between the items ranked 1 and 2.
HTH-
Chris
Chris Hohmann
Bob Barrows
> That should read "Paul may want to USE a string..."
Sorry, but that went right over my head ... :-)
What should read "Paul may want to USE a string..."?
Bob
Chris Hohmann
Bob Barrows
OK, either I'm missing a post you sent, or you neglected to send it ...
Bob
Paul Sorrell
Assuming RDMS = SQL Server?
Mine is an Access database, is this the reason why I cant make it work?
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'CASE Category WHEN 'SideBySide' THEN 0 WHEN 'Pair' THEN 1
WHEN 'HammerGun' THEN 2 WHEN 'UnderOver' THEN 3 WHEN 'DoubleRifle' THEN 4
WHEN 'Combination' THEN 5 WHEN 'Collectible' THEN 6 WHEN 'Auto' THEN 7 WHEN
'Rifle' THEN 8 WHEN 'Single' THEN 9 WHEN 'Airg'.
/gunshop/StockListPrintOut.asp, line 31
Here is my code.............
<%Set AllItems = Connect.Execute(_
"SELECT *
CASE Category
WHEN 'SideBySide' THEN 0
WHEN 'Pair' THEN 1
WHEN 'HammerGun' THEN 2
WHEN 'UnderOver' THEN 3
WHEN 'DoubleRifle' THEN 4
WHEN 'Combination' THEN 5
WHEN 'Collectible' THEN 6
WHEN 'Auto' THEN 7
WHEN 'Rifle' THEN 8
WHEN 'Single' THEN 9
WHEN 'Airgun' THEN 10
END As Rank
FROM
Items
ORDER BY Rank, Price DESC ")%>
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:urBi2RfU...@TK2MSFTNGP12.phx.gbl...
Chris Hohmann
Bob Barrows
whole bunch of nested Iif statements (not my idea of "quick and easy"
<wink> ):
ORDER BY IIF(Category = 'SideBySide',0,IIF(Category = Pair,1,IIF...))
HTH,
Bob Barrows
Chris Hohmann
> What is meant
> Assuming RDMS = SQL Server?
> Mine is an Access database, is this the reason why I cant make it
work?
Yes. When posting a database related question, it is often helpful to
indicate what database and version you are using. For Access you will
need to use IIF/SWITCH.
SELECT
.
.
.
ORDER BY
SWITCH
(
Category="SideBySide",0
Category="Pair",1
.
.
.
)
HTH
-Chris
Bob Barrows
> news:OBkPewgU...@TK2MSFTNGP10.phx.gbl
Ah - I wonder why I never got this message...
As to your point, can use the old programmer's trick of initially numbering
the ranks by 10: 10,20,30 ...
I usually just renumber them when the number of rows is this small.
Bob
Bob Barrows
IIF's
ORDER BY Switch(Category = 'SideBySide',0,Category = 'Pair',1,...)
Bob Barrows
Paul Sorrell
Thanks all thats perfect.
(anyone want to buy a gun?)
paul.
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:utocmbiU...@TK2MSFTNGP09.phx.gbl...