SitePoint Sponsor

User Tag List

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

    Unhappy MYSQL query need help.

    I need help on query the line of data that needed (Arrow).

    id a_time xgroup
    1 2012-07-05 09:30:00 2
    5 2012-07-05 09:35:00 1
    6 2012-07-05 10:35:00 1
    8 2012-07-05 10:35:05 1
    10 2012-07-06 04:08:00 2
    15 2012-07-06 09:10:00 2
    18 2012-07-06 12:45:00 1
    21 2012-07-06 13:15:00 2
    23 2012-07-06 14:00:00 1
    30 2012-07-06 08:00:00 2 <--
    33 2012-07-07 02:45:00 2
    35 2012-07-07 04:25:03 2
    40 2012-07-07 11:10:06 2

    Hi, I hope someone could help me how to query the row of data from above table.
    I need to query the line of data which highlighted with an ARROW.

    Please note that the ID is unique(Primary KeY), but for reason which data which is inserted with "INSERT IGNORE", the id will not align in sequence, thus ID will be jumped.
    As from the data table, there will be several group categories(column xGROUP) as 1, 2, 3 .....
    data from column "a_time" will be always collected as ascending because new data that collected will always the newest time ahead.

    Assume that i need to call out what is the id where the 'last categories' was insert into table(from the table showed, the last categories is 2), and the 1st ID from the categories which last insert categories is.

    I hope the structure of the table (arrow) and the sentences that i elaborate makes you understand what i wanted.

    Please help me how to query it out.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by smith randerson View Post
    ... the 1st ID from the categories which last insert categories is.
    Code:
    SELECT id 
         , a_time 
         , xgroup
      FROM daTable
     WHERE id =
           ( SELECT MIN(t.id) AS first_id
               FROM ( SELECT xgroup
                        FROM daTable
                       WHERE a_time =
                             ( SELECT MAX(a_time)
                                 FROM daTable ) ) AS last_category
             INNER
               JOIN daTable AS t
                 ON t.xgroup = last_category.xgroup )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, you are pro.


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
  •