"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

Urgent Please Help - Conserving Page Real Estate

When the page header properties are set as 'PrintOnFirstPage' = False, so
that it doesn't show on the first page, it still seems to take up space on
the first page.
Is there a way to compress this space?
The pseudo report header that I created in the body (to show up only on page
1) is showing up about an inch lower than it really should when the report
renders. I am thinking that this is probably because the page header space
is still being consumed, even though it isn't literally being printed there.
Am I right? Is there a way to conserve that page real estate?
Thanks,
LisaThis is a bug. I don't know of any way to collapse out that space until we
get the bug fixed.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:O4ZqFEFZEHA.4092@.TK2MSFTNGP11.phx.gbl...
> When the page header properties are set as 'PrintOnFirstPage' = False, so
> that it doesn't show on the first page, it still seems to take up space on
> the first page.
> Is there a way to compress this space?
> The pseudo report header that I created in the body (to show up only on
page
> 1) is showing up about an inch lower than it really should when the report
> renders. I am thinking that this is probably because the page header
space
> is still being consumed, even though it isn't literally being printed
there.
> Am I right? Is there a way to conserve that page real estate?
> Thanks,
> Lisa
>
>|||Thank you.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:u9OQBqFZEHA.3476@.tk2msftngp13.phx.gbl...
> This is a bug. I don't know of any way to collapse out that space until
we
> get the bug fixed.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:O4ZqFEFZEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > When the page header properties are set as 'PrintOnFirstPage' = False,
so
> > that it doesn't show on the first page, it still seems to take up space
on
> > the first page.
> > Is there a way to compress this space?
> >
> > The pseudo report header that I created in the body (to show up only on
> page
> > 1) is showing up about an inch lower than it really should when the
report
> > renders. I am thinking that this is probably because the page header
> space
> > is still being consumed, even though it isn't literally being printed
> there.
> > Am I right? Is there a way to conserve that page real estate?
> >
> > Thanks,
> >
> > Lisa
> >
> >
> >
>