View Full Version : how to do this

12-05-2007, 01:55 PM

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

12-05-2007, 01:58 PM
SELECT DISTINCT column_name(s)
FROM table_name

12-11-2007, 02: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


12-11-2007, 03:47 PM
SELECT DISTINCT column_name(s)
FROM table_name

This is not even helpful. It explains nothing.

SELECT productid, productname
products p
,(SELECT DISTINCT catid FROM products) cat
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.