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
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...
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
--
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...
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
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.....
>
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:u%23EZg8XU...@TK2MSFTNGP10.phx.gbl...
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
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?
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
>.
>
HTH-
Chris
Sorry, but that went right over my head ... :-)
What should read "Paul may want to USE a string..."?
Bob
OK, either I'm missing a post you sent, or you neglected to send it ...
Bob
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...
ORDER BY IIF(Category = 'SideBySide',0,IIF(Category = Pair,1,IIF...))
HTH,
Bob Barrows
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
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
ORDER BY Switch(Category = 'SideBySide',0,Category = 'Pair',1,...)
Bob Barrows
(anyone want to buy a gun?)
paul.
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:utocmbiU...@TK2MSFTNGP09.phx.gbl...