Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: Help needed to write a sql query

    I am having a hard time writing a sql query. Following is a mocked up schema and a description of what I am trying to do.

    Table 1 -> Col1, Col2, Col3, Col4, Col5
    Table 2 -> Col1, Col2, Col3, Col4, Col5

    The sql should do an inner join on Col1, Col2 and Col3 and group by on the same columns and display the value of Col5(from table 2) based on maximum of Col4(from table 2) in the group. Col4 is of data type timestamp.

    Hopefully my explanation was clear.

    Any help will be greatly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    It sounds like you know what you want to do, so give it a try. Write the query per your specs and see if it works. If it does not, please post your query, some sample data, what the results should look like and what the results of your query are.

    Rather than do the work for you, the posters on this forum would rather teach you how to do it yourself. So, give it a shot and see what happens.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.

    Post SQL you tried, the results & what improvement is needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by pokiman
    I am having a hard time writing a sql query. Following is a mocked up schema and a description of what I am trying to do.

    Table 1 -> Col1, Col2, Col3, Col4, Col5
    Table 2 -> Col1, Col2, Col3, Col4, Col5

    The sql should do an inner join on Col1, Col2 and Col3 and group by on the same columns and display the value of Col5(from table 2) based on maximum of Col4(from table 2) in the group. Col4 is of data type timestamp.

    Hopefully my explanation was clear.

    Any help will be greatly appreciated.

    Thanks in advance.
    from your description, my understandings is:

    SELECT y.Col1
    , y.Col2
    , y.Col3
    , max(y.Col4)
    , y.Col5
    FROM Table1 x, table2 y
    WHERE x.Col1=y.Col1
    AND x.Col2=y.Col2 AND x.Col3=y.Col3
    GROUP BY y.Col1 , y.Col2 , y.Col3
    ORDER BY max(y.Col4);
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please do not do peoples homework for them, they will never learn it. If they gave it a try and needed help, we will be glad to do it. But don't hand them a solution.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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