SQL query required

[Imtiaz Murtaza]

Friends, I got a table in which records are in the following manner:

category product
======== =======
clothes jeans
clothes shirts
clothes ties
weapons Machine Gun
weapons Missile
ColdDrinks Pepsi
ColdDrinks Coca Cola

Now i want to select a result from the above table in such a manner that that the first record of each "category" gets returned. For example, select result should be:

category product
======== =======
clothes jeans
weapons Machine Gun
ColdDrinks Pepsi

How can i do so ? Imtiaz

[wgdesigner]

Dear Friend, I don;t know which type of query u want, as ur mind asks.. but i have one query which will give u same result which u want.. select category,product from table_name where product in('jeans','machine gun','pepsi'); from taht u can view same result as u want, Raj Khatri

[Lost User]

Try the following SQL statement: SELECT Category, FIRST(Product) AS FirstOfProduct FROM YourTable GROUP BY Category; You may substitue FIRST() with either MIN() or MAX() if FIRST() is not supported. Hope that works... Go2Canada

[irshu]

Your solution is here it works fine for your problem. select distinct(category), (select top 1 product from myTable b where b.category = a.category) as 'Product' from myTable a