UNION order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya

I want to poulate a combobox with a list of names... however I want the top of the list to be "All Users" - I can get the union to work fine, but it will always sort the data so that anyone who's name is before "All Users" (eg Ahmed) will always be higher on the list than "All Users"

Is there a way that I can exclude the first part of the union from the sorting? This is my current code

SELECT "All Users" FROM tblUsers UNION SELECT DISTINCT tblUsers.UserName FROM tblUsers

Many thanks

MK
 
Try
SELECT "All Users" As UserName, 1 As Rectype FROM tblUsers
UNION SELECT DISTINCT tblUsers.UserName, 2 FROM tblUsers
ORDER BY RecType, UserName;


Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hiya,

I want to poulate a combobox with a list of names...
Click to expand...
however I want the top of the list to be "All Users" - I
can get the union to work fine, but it will always sort the
data so that anyone who's name is before "All Users" (eg
Ahmed) will always be higher on the list than "All Users".
Is there a way that I can exclude the first part of the
Click to expand...
union from the sorting? This is my current code:
SELECT "All Users" FROM tblUsers UNION SELECT DISTINCT
Click to expand...
tblUsers.UserName FROM tblUsers;
 
Smart... should've thought of that myself! Thanks

----- Gerald Stanley wrote: ----

Try
SELECT "All Users" As UserName, 1 As Rectype FROM tblUser
UNION SELECT DISTINCT tblUsers.UserName, 2 FROM tblUser
ORDER BY RecType, UserName


Hope This Help
Gerald Stanley MCS
-----Original Message----
Hiya
Click to expand...
however I want the top of the list to be "All Users" -
can get the union to work fine, but it will always sort th
data so that anyone who's name is before "All Users" (e
Ahmed) will always be higher on the list than "All Users"
 
Another option is to make "All Users" into " All Users" or "(All Users)" which
means you don't need the added sort column. Also, I wouldn't bother with the
DISTINCT since the UNION will return only Distinct records. If it didn't you
would have the same number of "All Users" as you have records in your tblUsers.


SELECT "(All Users)" FROM tblUsers UNION SELECT tblUsers.UserName FROM tblUsers;
 
Back
Top