"Order BY" question

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

tobias,

The best thing for you to do would be to have a lookup table that stores your "Status" values, and add a column called "SortOrder" to that table -- let this column define the order of the statuses. Then, in your query, join onto the Status table and do an ORDER BY SortOrder. This will also give you the flexibility of modifying the sort order to something else in the future.

|||

Thanks, vito. Forgive my lack of knowledge here, but could give an example of what the SQL select statement what look like that joined two tables and ORDER(ed) BY SortOrder? Thanks for your help.

|||

tobias5:

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

Vito's suggestion would be to build a lookup table, let's call it Status, that looks like this:

StatusCode SortOrder

evict 3
rehab 4
listing 1
escrow 2

Then your SQL statement would look like this:

SELECT

someColumns

FROM

myTable

INNER JOIN

Status ON myTable.StatusCode = Status.StatusCode

ORDER BY

Status.SortOrder

Alternately, and this is ultimately not as flexible, you could forego the additional table and use a CASE statement for the ordering:

SELECT

someColumns

FROM

myTable
ORDER BY

CASE StatusCode

WHEN 'evict' THEN 3

WHEN 'rehab' THEN 4

WHEN 'listing' THEN 1

WHEN 'escrow' THEN 2

ELSE 5

END

|||

Thanks vito and tmorton. Both of your suggestions worked beautifully.

I VERY much appreciate your help!

Ron