Advanced Search

Results 1 to 4 of 4

Thread: Ordering results in a union

  1. #1
    Join Date
    Nov 2006
    Location
    Northeast USA
    Posts
    408
    Thanks
    8
    Thanked 30 Times in 28 Posts

    Default Ordering results in a union

    Hello, I am currently using UNION to join two tables for a query, however the problem is that I would like all of the results to be ordered based on a shared column. Basically, right now each Selection query is being ordered by a integer, however when they are combined, all of the order is mixed up. Here is my sql:
    Code:
    (SELECT title, description, author, featured, 'snippet' AS tablename FROM snippet WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ") UNION (SELECT title, description, author, featured, 'script' AS tablename FROM script WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ")
    I cannot use array_sort in php because it goes through a while loop to display the results. Any suggestions?
    -Ben -- THE DYNAMIC DRIVERS
    My Links: My DD Profile||My Youtube Video Tutorials||DD Helping Coders||DD Coders In Training
    I told my client to press F5, the client pressed F, then 5, *facepalm*

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    80
    Thanked 89 Times in 87 Posts

    Default

    Code:
    (SELECT title, description, author, featured, 'snippet' AS tablename 
    FROM snippet WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ") 
    UNION 
    (SELECT title, description, author, featured, 'script' AS tablename 
    FROM script WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ")
    ORDER BY featured
    As you can see, if you want to sort all of the tables by a shared column such as 'featured' list the command after the last SELECT statement and after the closing parentheses.
    Last edited by james438; 04-25-2011 at 08:48 AM. Reason: slight clarification
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Nov 2006
    Location
    Northeast USA
    Posts
    408
    Thanks
    8
    Thanked 30 Times in 28 Posts

    Default

    What a belated response, I solved this problem 4 months ago btw. But thanks anyways! How did you manage to find this thread anyways?
    -Ben -- THE DYNAMIC DRIVERS
    My Links: My DD Profile||My Youtube Video Tutorials||DD Helping Coders||DD Coders In Training
    I told my client to press F5, the client pressed F, then 5, *facepalm*

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    80
    Thanked 89 Times in 87 Posts

    Default

    Yes, it is a very late response. I felt like working on a few coding puzzles, so I am just browsing through the MySQL history pages and answering ones that have not been answered and look interesting. Considering that people post so seldom in this MySQL forum your thread was only on like page 2.

    If nothing else, it is fun for me and it also may help someone who is browsing through the forum with a similar problem. Every once in a while I like to binge code like this.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

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