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?

Show a representative sample of data in the table and the output you want when this query is run.

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?

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);