SQLTeam.com | Weblogs | Forums

Improving CTE quesry with count and rownumber

i have a query, which it's results based in id and other columns.

i gave each row a row number and a count based on the id
WITH cte
AS (
SELECT ROW_NUMBER() OVER(Partition BY Id ORDER BY Id) as RowNo,
COUNT OVER(Partition BY Id ORDER BY Id) as count,
* FROM ......
.....
where (total=1) or (total>1 and rowcount>1)
how can i improve it so i don't use the row number and count (if possible)

Please provide sample data in the following


insert into #developer
select 1 , 'One' union
select 2, 'One' 

etc

there is a column id, that appears sometimes once and some times twice
(for example id=83680, and twice id=83677)
in my where i use the first 2 columns RowNo and total
where (total = 1) or (total>1 and rowNo=2)
which mean if the id appears only once take the row, if it more then once than take
the row with RowNo=2.
of this i used 2 calculation with count over partition and ROW_NUMBER over partition.
is there a better way to do it?

How do you know which row will end up being row2 if you ordering by ID. It will be random. You would need to order by something else. Until we have the answer to that we cannot give a solution

i use optimizedresult, column based on this i do the ROW COUNT

Should that not be used on the Row Number?

RowNo is :
ROW_NUMBER() OVER(Partition BY Id ORDER BY Id) as RowNo

How are you calculating 'optimizedResults'? Ideally - that should become a part of the ordering for the row_number function - then you can use just that in descending order. Example:

ROW_NUMBER() OVER(PARTITION BY id ORDER BY optimizedResults desc)

Then - in your where clause you select RowNo = 1. This will return the row with the highest optimizedResults value regardless of how many have the same ID value.