#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0

    Select latest rows from two tables by latest time and group by another column


    I need somebody's help...
    I have 2 tables table1 and table2 and i want an output table contains the latest records(by timestamp) and group by another column(c.category).itried with max(timestamp) but the timestamp colum has the maximum date but other columns has the data of the first entry of that category to the table

    i tried this but didnt get expected result
    SELECT c.category AS category, c.result1 AS result1, o.result2 AS result2,c.timestamp AS time1,
    o.timestamp AS time2,
    c.id AS id
    FROM table1 c
    left outer JOIN table2 o
    ON (c.id= o.Fid)
    c.timsstamp between '2013-02-01 00:00:00' and '2013-02-21 00:00:00' group by a.category
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Consider providing CREATE and INSERT statements for the relevant tables, together with the desired result set. Provide just enough data to be properly representative.

    And don't ask for help 'urgently' or 'as soon as possible'. In my experience, it tends to put people off.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    nobody answered my question..is that difficult to do?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by joetx
    ..is that difficult to do?
    it is if we have to guess
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    here is the tables and result tables looks like..same serial number can test more than one times. result table should be datetested desc order and no duplicated serial numbers allowed.

    Table1

    uniqueid serialnumber Testresult Datetested


    1 987654321 fail 2012-01-01 01:30:45
    2 987654321 fail 2012-01-02 02:40:55
    3 123456789 fail 2012-01-01 03:30:55
    4 123456789 fail 2012-01-02 04:30:45
    5 123456789 pass 2012-01-03 01:30:45
    6 987654321 Pass 2012-01-03 02:30:45


    Table 2

    TestID serialnumber Testresult Datetested


    5 123456789 fail 2012-01-04 06:30:55


    Result table

    TestID serialnumber Testresult Datetested


    5 123456789 fail 2012-01-04 06:30:55
    6 987654321 Pass 2012-01-03 02:30:45
  10. #6
  11. Old Fart
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Chicago
    Posts
    106
    Rep Power
    4
    Could you please show the table structure for the two input tables.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Table1

    uniqueid -primary key
    serial number - indexed

    table2

    testid -foreign key
    serial number indexed
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Table1

    uniqueid -primary key
    serial number - indexed

    table2

    testid -foreign key
    serial number indexed
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by joetx
    Table1

    uniqueid -primary key
    serial number - indexed

    table2

    testid -foreign key
    serial number indexed
    this does not correspond to your post with the sample data

    please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    this does not correspond to your post with the sample data

    please do a SHOW CREATE TABLE for each table

    Table1

    uniqueid (primary key) serialnumber(normal index) Testresult (int 0 fro fail and 1 for pass) Datetested(timestamp)



    Table 2

    TestID (primary key) serialnumber(normal index) Testresult (int 0 fro fail and 1 for pass) Datetested(timestamp)




    Result table

    TestID serialnumber Testresult Datetested
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    okay, you don't really understand what a SHOW CREATE TABLE is, do you

    going back to post #1, where are you getting the idea that these tables should be joined?

    you will notice that there are no columns called "id" or "Fid" in your tables

    what is the actual need for you to "group by another column(c.category)"?

    you will notice that there is no category column in your tables

    i'm sorry, i'm gonna bail on this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    okay, you don't really understand what a SHOW CREATE TABLE is, do you

    going back to post #1, where are you getting the idea that these tables should be joined?

    you will notice that there are no columns called "id" or "Fid" in your tables

    what is the actual need for you to "group by another column(c.category)"?

    you will notice that there is no category column in your tables

    i'm sorry, i'm gonna bail on this thread

    i got the answer for this no problem my script is running and got the latest rows ..in my #1 post i put dummy field names but later in #5 i mentioned the real field names ...if you dont know the answer leave it ..thanks

IMN logo majestic logo threadwatch logo seochat tools logo