11 Replies Latest reply on Jun 1, 2007 7:15 AM by insuractive

    CFQuery 'ORDER BY' question

    Hagster Level 1
      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

        • 1. Re: CFQuery 'ORDER BY' question
          Level 7
          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
          • 2. Re: CFQuery 'ORDER BY' question
            paross1 Level 2
            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
            • 3. Re: CFQuery 'ORDER BY' question
              Level 7
              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.

              • 4. Re: CFQuery 'ORDER BY' question
                scooter5791 Level 1
                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).
                • 5. Re: CFQuery 'ORDER BY' question
                  Level 7
                  You can create custom sort orders in Oracle:
                  http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch13custlocale.htm#i10 06766

                  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
                  • 6. Re: CFQuery 'ORDER BY' question
                    insuractive Level 3
                    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.
                    • 7. Re: CFQuery 'ORDER BY' question
                      Level 7
                      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.



                      • 8. Re: CFQuery 'ORDER BY' question
                        Dan Bracuk Level 5
                        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.
                        • 9. Re: CFQuery 'ORDER BY' question
                          Cole Level 1
                          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
                          • 10. Re: CFQuery 'ORDER BY' question
                            Hagster Level 1
                            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 :)
                            • 11. Re: CFQuery 'ORDER BY' question
                              insuractive Level 3
                              Ian,
                              I actually use the column alias in the order clause a fair amount. It works like a dream in MS SQL Server.