Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    2

    Help with mySQL subquery

    I have an SQL table "products" with fields like so:

    id = index
    pid = product id
    cat = category
    date = date product was updated
    data1 = data pertaining to the product
    data2
    data3
    data4
    data5

    I want to select data 1-5 for the latest update of each pid within a particular category. This table has multiple records of the same pid because a new record is added each time the product is updated, and the old ones are used for queries based on the history of the product. How would I write a select statement to do that?

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Show a representative sample of data in the table and the output you want when this query is run.

  3. #3
    Join Date
    May 2008
    Posts
    2
    id | pid | cat | date | data1 | data2 | data3 | data4 | data5
    1 | 1000| 1 |timestamp | 1.01 | string | string | string | string
    2 | 1000| 1 |timestamp | 2.2 | string | string | string | string
    3 | 1000| 1 |timestamp | 5 | string | string | string | string
    4 | 2000| 1 |timestamp | 1.2 | string | string | string | string
    5 | 3000| 1 |timestamp | 2.0 | string | string | string | string
    6 | 3000| 1 |timestamp | 3.3 | string | string | string | string
    7 | 4000| 2 |timestamp | 1 | string | string | string | string
    8 | 4000| 2 |timestamp | 2.5 | string | string | string | string
    9 | 5000| 3 |timestamp | 2 | string | string | string | string
    10| 5000 | 3 |timestamp | 4.6 | string | string | string | string

    OK so the idea is to retrieve the integer and the 4 strings for each of the unique products (pid) in a particular category if it has the newest time stamp.
    So for example I want to narrow down the data to a sample set. Where cat ="1" in cat 1 there are 3 products (pid) 1000, 2000, and 3000 but some of there products have been updated (1000, and 3000) so I want to take the one with the newest timestamp out of the bunch.

    So in the end I should get the values in fields data1, data2, data3, data4, and data5 for pid 1000, 2000, and 3000 but only for the record with the latest date of those products. Understand?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    SELECT p.*
    FROM table p
    INNER JOIN (SELECT pid, cat, max(date) FROM table GROUP BY pid, cat) as max
    ON (p.pid = max.pid AND p.cat = max.cat AND p.date = max.date);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

Posting Permissions

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