record sorting options

[Targa]

Im working on a small sales lead tracking application.

One of the features requested by my client is to sort records by status.
There are 6 different status values to which Ive assigned numbers - 1 thru
6.

I can sort simply by ascending or descending but is it possible to sort in
different ways?

For instance, if the user wants to display all the status 3 records first,
followed buy status 1, status 2, etc. Would look like this:
Status 3
Status 1
Status 2
Status 4
Status 5
Status 6

Can this be done?

Thanks

[Ken Schaefer]

Use a UNION query. Something like:

SELECT
Field1, Field2, 1
FROM
myTable
WHERE
Status = 3
UNION
SELECT
Field1, Field2, 2
FROM
myTable
WHERE
Status = 1
ORDER BY
3 -- orders by the appended number

Cheers
Ken



"Targa" <targa1SPAMSUCKS@alltel.net> wrote in message
news:uXUTuLugDHA.524@tk2msftngp13.phx.gbl...
: Im working on a small sales lead tracking application.
:
: One of the features requested by my client is to sort records by status.
: There are 6 different status values to which Ive assigned numbers - 1 thru
: 6.
:
: I can sort simply by ascending or descending but is it possible to sort in
: different ways?
:
: For instance, if the user wants to display all the status 3 records first,
: followed buy status 1, status 2, etc. Would look like this:
: Status 3
: Status 1
: Status 2
: Status 4
: Status 5
: Status 6
:
: Can this be done?
:
: Thanks
:
:

[Ray at]

I think this will work.

SELECT TABLE1.RecordTitle, TABLE1.Status FROM
(SELECT S.ID, CASE S.Status WHEN 3 THEN 0 ELSE 1 END AS ShowTop FROM
TABLE1 S) AS ST
INNER JOIN TABLE1 ON ST.ID=TABLE1.ID
ORDER BY ST.ShowTop, TABLE1.Status

Ray at home



"Targa" <targa1SPAMSUCKS@alltel.net> wrote in message
news:uXUTuLugDHA.524@tk2msftngp13.phx.gbl...

> Im working on a small sales lead tracking application.
>
> One of the features requested by my client is to sort records by status.
> There are 6 different status values to which Ive assigned numbers - 1 thru
> 6.
>
> I can sort simply by ascending or descending but is it possible to sort in
> different ways?
>
> For instance, if the user wants to display all the status 3 records first,
> followed buy status 1, status 2, etc. Would look like this:
> Status 3
> Status 1
> Status 2
> Status 4
> Status 5
> Status 6
>
> Can this be done?
>
> Thanks
>
>

[Ray at]

I think you meant this, but correct me if I'm wrong.

SELECT Field1, Field2, 1 FROM myTable WHERE Status=3 UNION SELECT Field1,
Field2, 2 WHERE STATUS <> 3 ORDER BY 3, Status

Ray at home

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:ejRYR1wgDHA.1748@TK2MSFTNGP10.phx.gbl...

> Use a UNION query. Something like:
>
> SELECT
> Field1, Field2, 1
> FROM
> myTable
> WHERE
> Status = 3
> UNION
> SELECT
> Field1, Field2, 2
> FROM
> myTable
> WHERE
> Status = 1
> ORDER BY
> 3 -- orders by the appended number
>
> Cheers
> Ken
>
>
>
> "Targa" <targa1SPAMSUCKS@alltel.net> wrote in message
> news:uXUTuLugDHA.524@tk2msftngp13.phx.gbl...
> : Im working on a small sales lead tracking application.
> :
> : One of the features requested by my client is to sort records by status.
> : There are 6 different status values to which Ive assigned numbers - 1

thru

> : 6.
> :
> : I can sort simply by ascending or descending but is it possible to sort

in

> : different ways?
> :
> : For instance, if the user wants to display all the status 3 records

first,

> : followed buy status 1, status 2, etc. Would look like this:
> : Status 3
> : Status 1
> : Status 2
> : Status 4
> : Status 5
> : Status 6
> :
> : Can this be done?
> :
> : Thanks
> :
> :
>
>

[Ken Schaefer]

No, I meant what I wrote. :-)

I was just trying to give a simple example of putting records where Status =
3 ahead of records where Status = 1. The OP could then append another query
to put Status = 2 records after the Status = 1 records (and so forth for all
the specified statii, or is that statuses?).

The addition of the ORDER BY ... Status doesn't add anything to the query,
since you are already ordering the records by an arbitrary value (in the 3
column) based on the value of the status field of that record. You could
order by other fields though.

So:

SELECT
Field1
, Field2
, 1
FROM
myTable
WHERE
Status = 3
UNION
SELECT
Field1
, Field2
, 2
FROM
myTable
WHERE
Status = 1
UNION
SELECT
Field1
, Field2
, 3
FROM
myTable
WHERE
Status = 2
ORDER BY
3
, Field1
, Field2


Cheers
Ken

"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:uw6$OQxgDHA.2408@TK2MSFTNGP09.phx.gbl...
: I think you meant this, but correct me if I'm wrong.
:
: SELECT Field1, Field2, 1 FROM myTable WHERE Status=3 UNION SELECT Field1,
: Field2, 2 WHERE STATUS <> 3 ORDER BY 3, Status
:
: Ray at home
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:ejRYR1wgDHA.1748@TK2MSFTNGP10.phx.gbl...
: > Use a UNION query. Something like:
: >
: > SELECT
: > Field1, Field2, 1
: > FROM
: > myTable
: > WHERE
: > Status = 3
: > UNION
: > SELECT
: > Field1, Field2, 2
: > FROM
: > myTable
: > WHERE
: > Status = 1
: > ORDER BY
: > 3 -- orders by the appended number
: >
: > Cheers
: > Ken
: >
: >
: >
: > "Targa" <targa1SPAMSUCKS@alltel.net> wrote in message
: > news:uXUTuLugDHA.524@tk2msftngp13.phx.gbl...
: > : Im working on a small sales lead tracking application.
: > :
: > : One of the features requested by my client is to sort records by
status.
: > : There are 6 different status values to which Ive assigned numbers - 1
: thru
: > : 6.
: > :
: > : I can sort simply by ascending or descending but is it possible to
sort
: in
: > : different ways?
: > :
: > : For instance, if the user wants to display all the status 3 records
: first,
: > : followed buy status 1, status 2, etc. Would look like this:
: > : Status 3
: > : Status 1
: > : Status 2
: > : Status 4
: > : Status 5
: > : Status 6
: > :
: > : Can this be done?
: > :
: > : Thanks
: > :
: > :
: >
: >
:
:

[Bullschmidt]

Perhaps add a SortNum field to the Status table and sort on that instead of the
Status field itself.