List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:April 14 2004 5:19am
Subject:Re: Multiple SELECTs in one query
View as plain text  
A * by itself must come first, so

   SELECT *, "A" AS SortCode FROM Jobs

will work.

Udikarni@stripped 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


Thread
Multiple SELECTs in one querySteve Pugh12 Apr
RE: Multiple SELECTs in one queryVictor Pendleton12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh12 Apr
  • "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
    • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
        • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
          • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
            • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
              • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
        • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
    • Re: "INSERT INTO" dropping slashes from stringsDan Nelson5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
  • Re: "INSERT INTO" dropping slashes from stringsLou Olsten8 May
Re: Multiple SELECTs in one queryUdikarni12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh14 Apr
  • Re: Multiple SELECTs in one queryHarald Fuchs14 Apr
Re: Multiple SELECTs in one queryUdikarni14 Apr
  • Re: Multiple SELECTs in one queryMichael Stassen14 Apr