Yargh! Sorting UNION'd SQL query 
Author Message
 Yargh! Sorting UNION'd SQL query

Hello all, I've got my sorting and grouping almost sussed out, but am a bit
flummoxed by the following factoid:

My query is two subqueries UNION'd together so that the code tests all jobs
matching its TEAM field, then goes to all jobs NOT matching.  I'd like the
jobs in Query1 to sort by Priority descending (100 first, 0 last), and then
the jobs in Query2 sorting the same way (but *after* query1, you know?)

Here's what I have:

JOBSQL = "SELECT * from Jobs WHERE Team = '" & eRs.Fields("Team") & "' ORDER
BY Priority DESC UNION ALL SELECT * From Jobs WHERE Team <> '" &
eRs.Fields("Team") & "'"
Set jrs = db.OpenRecordset(JOBSQL, dbOpenSnapshot)

First off, is "WHERE Team <> eRs.fields("Team") a valid way to search for
outsiders?  Not a{*filter*} of this in VB Help or Books Online, much to my

Second, if I sort the second query by priority then *everything* gets all
mixed together, not what I'm looking to do.  Help?  Ideas?  Small flightless

Fri, 04 Jan 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Problem sorting a UNION query with Oracle

2. Union query gives 'Too few parameters'

3. ODBC Failed with 'UNION' in query

4. UNION queries in SQL

5. Big speed problem with a UNION SQL query

6. HELP: Parametrized union queries don't work with Jet 4.0

7. Questions about sql union query in VB

8. SQL query works, Crystal query doesn't

9. Can't get Sorted Recordset using Query

10. Can't sort result set on query based on calculated values

11. SQL parameter query and sorting

12. Can't update sorted access query from vb6 using ado


Powered by phpBB® Forum Software