how to do this

xiaodao · 12-05-2007, 12:55 PM

Hi

i have a table `product` with structure like

productid productname catid
1 apple 1
2 pear 1
3 pork 2
4 beef 2
5 mutton 2
6 cake 3
7 bread 3

how can i get only one record from each catid '1' , '2' , '3' either randomly or most new one? thanks

nikos101 · 12-05-2007, 12:58 PM

Code:

SELECT DISTINCT column_name(s)
FROM table_name

bazz · 12-11-2007, 01:51 PM

if you want to find the newest one, I think you will need to have a field/column which has a time based value. Thiis could be the time it was added or some other relevant time.

Make sure you use the ISO date ofrmat and that you format the column to be a date instaed of a 'varchar' or 'int' for example. Formatting the column correctly is vital if you want to be compare, relate or add/subtract dates

bazz

aedrin · 12-11-2007, 02:47 PM

Quote:

Originally Posted by nikos101

Code:

SELECT DISTINCT column_name(s)
FROM table_name

This is not even helpful. It explains nothing.

Code:

SELECT productid, productname
FROM
    products p
    ,(SELECT DISTINCT catid FROM products) cat
WHERE
    p.catid = cat.catid

This might work. It uses a subquery to produce a list of categories, then it finds one product for each category. If it doesn't work, it should give you something to play around with.