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 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Sep 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Join problem on MySQL

    MYSQL: 3.23.36 running on LINUX

    I've got two tables:

    Table: Titles
    Fields: ID, title, index

    Table: Values
    Fields: ID_title, value, hour


    The first table has the unique list of Titles, the second table has
    the values each title has on a specific hour. There are several
    records on the second table matching the first table. How can I select
    the LAST value for each title on the first table, that has
    index="abc"? (LAST means with the bigger hour)

    I've tried:
    -> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
    Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
    group by Titles.ID
    It returns the firsts values for each title found on the second table.

    -> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
    Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
    group by Titles.ID order by Values.hour
    It returns the same values that the one above but ordered by hour
    DESC.

    -> SELECT Titles.ID, Titles.title, Values.value, max(Values.hour) from
    Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
    group by Titles.ID order by Values.hour
    It returns the hour's that I want, but the values are still the first
    ones and not the ones related to the hour returned...

    -> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
    Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
    It returns all the values with repective hour for all the titles. (If
    I could just filter theese results to get just the last value for each
    Title)

    Any ideas on how to do this??
    I'm starting to crash my head on the wall on this...

    PS: Sorry about the bad english

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    What kind of datatype is the field for the hour?
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    New to the CF scene
    Join Date
    Sep 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The data type for "hour" is time

  • #4
    Regular Coder
    Join Date
    Sep 2002
    Location
    British Columbia
    Posts
    235
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you could try

    SELECT Titles.ID
    FROM Titles LEFT JOIN Values ON Titles.ID = Values.ID_title
    WHERE Titles.value = 'abc'
    ORDER BY hour DESC LIMIT 1

    which will order the results by the numberical value of hour and do that in descending order, and then limits the returned result to only one row, being the one you want.


  •  

    Posting Permissions

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