[thelist] SQL ORDER BY

mattias at thorslund.us mattias at thorslund.us
Thu Apr 20 14:01:41 CDT 2006


Another possiblity is, if your statuses reside in a separate "lookup" 
table, you
can add a sort order column in that table. My pseudo-code:

Table Classes
----------------------------
ClassID int (primary key)
ClassName varchar(20)
SortOrder int

The data in this table would look like:

ClassID    ClassName    SortOrder
1          Open         10
2          Closed       20
3          Suspended    30

You would use it in yous statement like this:

SELECT
  [fields]
FROM
  MainTable as M
  [joins, etc.]
  LEFT OUTER JOIN Classes as C ON M.ClassID = M.ClassID
ORDER BY C.SortOrder, [other fields]


As should be obvious in the example above, you could also simply order by
C.ClassID. However, if you should ever need to change the order, or insert a
new status, the SortOrder column accommodates that much easier (re-numbering
the IDs means you need to also update the rows in your main table)...

HTH, HAND,

Mattias


Quoting j s <jslist at sbcglobal.net>:

> Hi,
>
> In asp when you do a Select statement - is there anyway to have it 
> ORDER BY specific words in a column?
> In the class column it says Open or Close or Suspended.  With a 
> Select statement can I tell it to ORDER BY Class  - Open then Close 
> then Suspended?
>
> Jess
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>






More information about the thelist mailing list