partition by clause
An example using using partition by clause References : http://msdn.microsoft.com/en-us/library/ms189461.aspx The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column Category. The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned. Question : I need to get one item each from a category Answer: select* from ( select row_number() over(partition by Category order by ItemName) AS IDv,* from ( select 'Apple' ItemName,'Fruits'Category UNION ALL select 'Banana' ItemName,'Fruits'Category UNION ALL select 'Turnip' ItemName,'Vegs'Category UNION ALL select 'Tomato' ItemName,'Vegs'Category UNION ALL select 'Eggplant' ItemName,'Vegs'Category ) A )B where b.