CFQuery 'ORDER BY' question

[Hagster]

Hello all
for simplisity lets say I have a DB Table with two fields 'type' and 'childpart' one is 'char' the other is 'numeric'
The char field would consist only 1 of three possible strings AN, AR or IN, the other is just numerical part numbers

the query would be

SELECT type, childpart,
FROM tablename
ORDER BY type, childpart

This would return me all the 'AN' then all the 'AR' then all the 'IN'

Can you specify the order to return the results in the Query so for example the records would be returned in
IN, AN, AR order ? rarther that the standard ASC / DESC order

Kind Regards Guy

[Newsgroup_User]

no, not with just ORDER BY clause.
you can:
a) use a union query to select specific record ranges in the order you want
b) use QoQ to select specific ranges of records from your main query
(which selects all records) and then output them separately
--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[paross1]

One way would be to create an extra column that you can populate with a value that you can use as your "arbitrary" sort value.

Phil

[Newsgroup_User]

If your DBMS supports it, you can use a CASE statement to create column
on the the fly that can then be used to sort the data.

[scooter5791]

Another way would be to create a stored procedure that would create a temp table, run the separate queries and dump the reults into the temp table, run a final get query against the temp table getting all the records that were dumped in (which would already be sorted), drop the temp table and finally return the query results. This would give you one query containing exactly what you want. You didn't say what database you were using so this may not be an option (i.e. Access).

[Newsgroup_User]

You can create custom sort orders in Oracle:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch13custlocale.htm#i1006766

You have not been able to do so in SQL Server since v 6.5, apparently. I'm
not sure why they took this feature out: it sounds quite handy.

I guess that generally a custom sort order can be derived from existing
columns using the techniques others on this thread have mentioned.

Google "[your DB here] custom sort", and you will find a whole bunch of
info.

--
Adam

[insuractive]

paross1 and Ian have the solution I would recommend.

The following should work in MS SQL (assuming I haven't typo-ed):

SELECT type, childpart', 'sortorder' = case when type = 'IN' then 1 when type = 'AN' then 2 else 3 end
FROM tablename
ORDER BY sortorder

not sure if "type" is a reserved word or not, though.

[Newsgroup_User]

insuractive wrote:
The following should work in MS SQL (assuming I haven't typo-ed):

SELECT type, childpart', 'sortorder' = case when type = 'IN' then 1
when type = 'AN' then 2 else 3 end
FROM tablename
ORDER BY sortorder


I do not do this very often, but I have a vague memory that you may not
be able to ORDER BY a inline select like that. If you have trouble with
that you my try moving the case statement to the ORDER BY clause.

ORDER BY case when type = 'IN' then 1 when type
= 'AN' then 2 else 3 end

And I think to proper syntax of the select statement would be.

SELECT type, childpart', case when type = 'IN' then 1 when type = 'AN'
then 2 else 3 end AS 'sortorder'

I'm not sure of the DBMS specific syntax for the case function, but I'm
sure a quick Google would turn it up.

[Dan_Bracuk]

quote:

---

Originally posted by: Newsgroup User
insuractive wrote:
The following should work in MS SQL (assuming I haven't typo-ed):

SELECT type, childpart', 'sortorder' = case when type = 'IN' then 1
when type = 'AN' then 2 else 3 end
FROM tablename
ORDER BY sortorder


I do not do this very often, but I have a vague memory that you may not
be able to ORDER BY a inline select like that.

---

I do it all the time. It may be db specific, but the two db's I use the most, redbrick and oracle allow column alias names in the order by clause.

[Cole]

create another table (hacktable) that with 2 fields

create table [tbl_order_hack] (
[order_id] [bigint] not null ,
[order_key] [varchar] (5)
)

insert into tbl_order_hack (order_id, order_key) values (1, 'IN')
insert into tbl_order_hack (order_id, order_key) values (2, 'AN')
insert into tbl_order_hack (order_id, order_key) values (3, 'AR')

order_key will be the values you want to order (AN,AR,IN)
order_id will be the order you want them to be returned in

then join your two tables on tbl_order_hack.order_key and tablename.type

Example

select tablename.type, tablename.childpart
from tablename left outer join tbl_order_hack on tablename.type = tbl_order_hack.order_key
order by tbl_order_hack.order_id

[Hagster]

Thanks all for your replies.
I have been using 1 large query and then query of querys and then multiple outputs to do this, I was just hoping there was a tidier way.

Im gonna play with some of the other methods just to compare etc.

Any way thanks again for the input 🙂

[insuractive]

Ian,
I actually use the column alias in the order clause a fair amount. It works like a dream in MS SQL Server.