June 9, 2015 at 8:27 am
Here is a CTE query
With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo
The results from Query1 and Query2 overlap sometimes.
The result set looks like:
1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000
1 Item2 20000
1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".
How would I obtain a record set that returns just the first occurrence of the ItemNo field?
Thanks,
pat
June 9, 2015 at 8:55 am
Quick suggestion, add another CTC and filter the output from there.
😎
June 9, 2015 at 8:56 am
mpdillon (6/9/2015)
Here is a CTE query
With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo
The results from Query1 and Query2 overlap sometimes.
The result set looks like:
1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000
1 Item2 20000
1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".
How would I obtain a record set that returns just the first occurrence of the ItemNo field?
Thanks,
pat
Have you tried just using UNION rather than UNION ALL? Using UNION on its own doesn't include duplicate rows. You also can't use the ROW_NUMBER in the where clause of the query where it is created. If you put
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo
in another CTE then select from that where RowNo = 1 it should work.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2015 at 9:29 am
Thanks for your replies. I should have thought about the UNION. I will try that now.
I do not understand how to create a CTE from a CTE. Could you reference a web page with an example or modify my code briefly.
Thank you,
pat
June 9, 2015 at 9:50 am
Couldn't find a great link but I've heard it referred to as "chained CTEs" before
;With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
, mstrTable_2 (RowNo, ItemNo, Sales )
AS (
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
)
SELECT*
FROMmstrTable_2
WHERERowNo = 1
order by ItemNo;
_____________________________________________________________________
- Nate
June 9, 2015 at 10:15 am
mpdillon (6/9/2015)
Thanks for your replies. I should have thought about the UNION. I will try that now.I do not understand how to create a CTE from a CTE. Could you reference a web page with an example or modify my code briefly.
Thank you,
pat
Quick example
😎
With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
,CTE2 AS
(
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
)
SELECT
*
FROM CTE2
WHERE RowNo = 1
order by ItemNo
June 9, 2015 at 11:14 am
Both solutions worked. The Union only worked fine. So did the double CTE. I didn't know that (a second CTE) could be done. The key seems to be the comma. Thank you for demonstrating that for me.
pat
June 9, 2015 at 11:30 am
mpdillon (6/9/2015)
Both solutions worked. The Union only worked fine. So did the double CTE. I didn't know that (a second CTE) could be done. The key seems to be the comma. Thank you for demonstrating that for me.pat
No worries and glad to help. The rule with the CTE's is that it has to be an unbroken chain, separated by a comma with no other statements allowed in between.
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply