"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

"Find In" Comma Separated Value

Hi,

****SQL Server related question.

I have a table in which one of the columns (col1) holds a string, like: 1,2,3,4,5,6,7,8,9,10

I am passing an int value (@.intValue) to the sproc.

What I want to be able to do is query the table like...

SELECT * FROM myTable where @.intValue ... is in col1

Any ideas?

Thanks a lot!!!!you can do something like

SELECT * FROM myTable WHERE col1 LIKE '%' + convert(varchar,@.intValue) + '%'

hth|||Thanks for the response ndinakar!

I tried your suggestion...

It does work...to a point. However... in my concatenated string, it is a possibility that I will have something that looks like this.

3,4,5,34,6,21

Then, when I query this table...say I am looking for '2' ...it returns the record with 21 in it.

Any other ideas about how I can go about this? I am thinking that I might have to break this concatenated value into separate columns.

Thanks for the reply. I greatly appreciate it!|||same approach, but you need to insure you have leading and trailing seperators

from this:
3,4,5,34,6,21

to this:
,3,4,5,34,6,21,

now you search for %,val,%

or in your example ,2, which wont match ,21,|||Thanks a lot! That works great...exactly what I was looking for.