Hi,

I have a table contains information related to sales:

SO number Order Date     Customer SellingPerson

1001          2012/07/02     ABC          Andy

1002          2012/07/02     XYZ           Alan

1003          2012/07/02     EFG          Almelia

1004          2012/07/02     ABC         John

1005          2012/07/02     XYZ          Oliver

1006          2012/07/02     HIJ           Dorthy

1007          2012/07/02     KLM          Andy

1008          2012/07/02     NOP         Rowan

1009          2012/07/02     QRS          David

1010          2012/07/02     ABC          Joey

Now, i want to write a query using CTE that gives me first five distinct customer in result set:

SO number Order Date     Customer SellingPerson

1001          2012/07/02     ABC          Andy

1002          2012/07/02     XYZ           Alan

1003          2012/07/02     EFG          Almelia

1006          2012/07/02     HIJ           Dorthy

1007          2012/07/02     KLM          Andy

I wrote this query :

With t(so_number,order date,customer, SellingPerson)
as
  (select top 5 so_number,order date,customer, SellingPerson from t)
 select distinct billingcontactperson from t order by so_id

and getting this error:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.

First, can it be done through CTE, if yes please suggest me solution.

If not, then suggest me the other solution.

Thanks.

Try the below psuedocode. You may add your acolumns:

create Table Sales(SONumber int, Customer varchar(10))
Insert into Sales
Select 1001,'ABC'
Union All
Select 1002,'XYZ'
Union All
Select 1004,'ABC'
Union All
Select 1005,'XYZ'
Union All
Select 1006,'HIJ'
With cte
AS
(
	Select *,ROW_NUMBER()Over(PARTITION by Customer Order by SONUMBER) Rn
		From Sales
)Select * From cte where Rn=1

There is a minor typo in the above - you need to add a ; before the with keyword.

Also, if you only need the top 5 customers, you can add a TOP 5  and ORDER BY to the last select -

;With cte
AS
(
	Select  *,ROW_NUMBER()Over(PARTITION by Customer Order by SONUMBER) Rn
		From Sales
)Select top 5 * From cte where Rn=1 ORDER BY SONumber

Thanks Steen

Thank you so much Lateesh!!

But just one question, Why you put 'Union All' while inserting records in table?

Ohhh, that does not matter, just used to have duplicate values while testing then realized that it does not matter as per your requirement.

Note: hope you know the difference between Union and Union All.

Yeah i know, and thanks again. :)

Sorry for resurrecting this post.

I have a situation where I need to do something similar. I don't care about first 5 distinct, I need all distinct. And I need all distinct by 2 columns. You can imagine SONumber in original dataset not having unique values and consider I need ALL rows with unique combination of (SONumber, Customer)

Thing is I'm "told" not to use DISTINCT on a table since it is not efficient. Fact of the matter is I already have  a CTE that yields me limited data like (1,2),(1,3),(1,3),(2,1),(2,2) and I want to get back (1,2),(1,3),(2,1),(2,2). So I would have thunk DISTINCT on a CTE that already has limited data wouldn't even need a temp table to be created behind the scenes, and even if it did, would it really matter if I (say) had at most 20 rows?

Do I need to use ROW_NUMBER() on the first CTE, to eliminate the duplicates into another CTE and then join to whatever other tables I want? If so, how do I write CTE to eliminated duplicates by 2 columns? There is 1 to many relationship between 1st column and 2nd column

• Edited by VintageFreak 7 hours 6 minutes ago