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 "*".
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
Instead of reading the table 4 times, each time taking a slice and labeling it A/B/C/D
you read it once and during that one pass you attach to each row the code of A/B/C/D
depending on its content and you're done. One pass instead of 4 and no UNIONs.
You might have to tinker with the syntax if CASE is not available in MySQL to this
extent but that's the general idea.
In a message dated 4/13/2004 6:50:33 PM Eastern Daylight Time, [EMAIL PROTECTED]
writes:
>
> Hey gang, many thanks to all for pointing me in the right direction for
> my previous "multiple selects" question. I moved to 4.1.1 and
> implemented Udikarni's use of multiple sum()s instead of multiple
> selects() and that stuff is all groovy now!
>
> Of course, I'm beating my head on *another* wall now...wouldn't ya just
> know it?
>
> My client code checks the main table for a few different criteria, and I
> used an additional "hard" select for a sorting method. Basically, each
> client looks for jobs to process, starting with jobs under its "default
> project" and "default jobtype", and then by its "default project" and
> all other jobtypes, and finally everything else. Within each of these
> sets, jobs are sorted by a "Priority" field.
>
> My previous query looked like this (butchered pseudocode follows):
>
> "SELECT "A" AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND
> Jobs.JobType = MyJobType
> UNION ALL SELECT "B" AS SortCode, * FROM Jobs WHERE Jobs.Project =
> MyProject AND Jobs.JobType <> MyJobType
> UNION ALL SELECT "C" AS SortCode, * FROM Jobs WHERE Jobs.Project <>
> MyProject AND Jobs.JobType = MyJobType
> UNION ALL SELECT "D" AS SortCode, * FROM Jobs WHERE Jobs.Project <>
> MyProject AND Jobs.JobType <> MyJobType
> ORDER BY SortCode ASC, Jobs.Priority ASC
>
> Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect
> that I'm doing something wrong with that pesky asterisk, because the
> following works:
>
> "SELECT "A" AS SortCode, JobName FROM Jobs"
>
> But the following does not:
>
> "SELECT "A" AS SortCode, * FROM Jobs"
>
> From what I can see in the MySQL.org docs, this should work...any
> ideas? If I can get around that, I suspect that my UNIONS will work OK
> and all will be well in the world....one can hope?!?
>
> As before, many thanks for any insight that y'all can provide!!
>
> Steve
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
