If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Help with mySQL subquery

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Location: 221B Baker St.
Posts: 487
Show a representative sample of data in the table and the output you want when this query is run.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On