test sql

0

Please login or register to vote for this query.

(click on this box to dismiss)

Woodworking

Q&A for professional and amateur woodworkers

declare @table table
(mark int, 
name varchar(10), 
cost int, 
salecategory varchar(10), 
costcategory varchar(10))

insert @table values(10, 'pete', 20, 'b', 'b')
insert @table values(10, 'pete', 20, 'b', 'b')
insert @table values(15, 'pete', 20, 'c', 'b')
insert @table values(10, 'pete', 56, 'c','b')
insert @table values(15, 'John', 10, 'd','e')
insert @table values(20, 'John', 10, 'a','b')
insert @table values(20, 'John', 10, 'd','d')

SELECT 
mark,
name,
cost,
salecategory,
costcategory,
dense_rank() over 
(PARTITION BY name ORDER BY name asc, cost desc, (case when salecategory!=costcategory then 0 else 1 end) desc) as number
FROM @table

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results