SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    AdCaliber Magic2K2's Avatar
    Join Date
    Nov 2001
    Location
    Northern Virginia
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Order by something I don't want to select?

    I have a table something like this for all intents & purposes:

    id, date, player, opponent, time
    1 1/1/07 joe jeff 8:00
    2 12/31/07 joe jeff 7:00
    3 1/1/07 bob jim 7:00

    Now I can search the table using date and I need to return an output like:

    bob 7:00
    jim
    joe 8:00
    jeff

    Looks easy, but bear with me. I want to return the results 2 rows per record as you can see above so I use a UNION query:

    Code:
    (SELECT player AS name, time FROM table WHERE date = '1/1/07') UNION (SELECT opponent AS name, '' AS time FROM table WHERE date = '1/1/07')
    The problem is I want to keep Bob & Jim together and keep Joe & Jeff together while sorting by time! Since time is selected as '' (empty string) for the 2nd of the two players, I cannot do this because it will output like this:

    jim
    jeff
    bob 7:00
    joe 8:00

    Now it would be easy to do if I were to just select id in each query and order by id, however I would rather not select additional columns. The reason is that this query is part of a very intricate program which takes each selected column and uses that in the output and I don't want to output id so I only select the columns I want to output. For the same reason, I select '' AS time for the 2nd player so the output isn't repetitive. I'm thinking I may have to modify the whole script though given this dilemma. Any ideas before I go ahead and do that? Thanks!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you need to do this in your host language


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
  •