Re: Multiple SELECTs in one query

[Michael Stassen]

A * by itself must come first, so

  SELECT *, "A" AS SortCode FROM Jobs

will work.

[EMAIL PROTECTED] wrote:

I am not sure about MySQL but in Oracle this will NOT work:  "SELECT "A" AS SortCode,      
* FROM Jobs"
However, this WILL:                                          "SELECT "A" AS SortCode, 
Jobs.* FROM Jobs"
Try adding the table or alias in front of the "*".
This works in MySQL, as well.

In general, however, I will repeat my suggestion from before - try to do
everything in one pass - it's much more efficient.
Something like this:

"SELECT (CASE 
              WHEN Jobs.Project =  MyProject AND Jobs.JobType =  MyJobType  then "A"
              WHEN Jobs.Project => MyProject AND Jobs.JobType <> MyJobType  then "B"
              WHEN Jobs.Project <> MyProject AND Jobs.JobType =  MyJobType  then "C"
              WHEN Jobs.Project <> MyProject AND Jobs.JobType <> MyJobType  then "D"
         END CASE
        )  SortCode,
        Jobs.*
ORDER BY SortCode      ASC,
         Jobs.Priority ASC

<SNIP>
You might have to tinker with the syntax if CASE is not available in
MySQL to this extent but that's the general idea.
MySQL has CASE, with almost the same syntax you describe, except it ends 
with END rather than END CASE.  See 
<http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html>.

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]