SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    because mysql does not support subselects, you cannot do it with the normal solution

    mysql.com has an article for how to do it, involving a temp table

    see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

    rudy
    http://rudy.ca/


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
  •