Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Posts
    122
    Thanks
    1
    Thanked 0 Times in 0 Posts

    SQL Union and Sort trouble

    I am using Union to create an Recordset from multiple Tables, but I would like the Recordset to be sorted by the Tables from which they came, ie.

    SELECT * FROM EmployeesUSA
    UNION SELECT * FROM EmployeesEU
    Sort by (create recordset from USA THEN create recordset From EU)

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    I am unaware of any way to do this without referencing exact field names.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    it seems to me that you should have a single table with a single column to differentiate between the employees.

    As it is you could do the following:

    Code:
    select
    foo,
    bar,
    qux,
    'table A'
    from tableA as sortbythis
    
    union all
    
    select
    foo,
    bar,
    qux,
    'table B'
    from tableB
    
    order by
    sortbythis,
    foo,
    bar
    the 'tableA' and 'tableB' are going to show up just like that. you will then sort by that column and then by the foo and bar column.

    The foo, bar, qux of course represent column names where you would substitute your correct ones.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •