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.idv=1