CODEWITHSUNDEEP
sql-servert-sql
Asynchronous
Asynchronous

Reputation: 3977

Retrieving specific number of rows based on sum of row number

After reading an experimenting I decided I need to ask:

I am trying to retrieve a specific number of rows from a table based on the sum of the row number: This is a basic table with two columns: CusID, CusName.

I started by numbering each row to 1 so that I can use a SUM of the row number, or so I thought.

WITH Example AS
(
    SELECT  
       *, 
       ROW_NUMBER() OVER (Partition by CusID ORDER BY CusID) AS RowNumber
    FROM 
       MySchema.MyTable
)

I am not sure how to move beyond here. I tried using the HAVING clause but obviously that would not work. I could also use TOP or Percent.

But I would like to retrieve the rows based on the sum of row number.

What's the way to do this?

Upvotes: 2

Views: 279

Answers (1)

user2724280
user2724280

Reputation:

First of all Windowed functions cannot be used in the context of another windowed function or aggregate.So you can not use Aggregate function inside the row_number I think it could better than use all function after your with like this

 WITH Example AS
(
SELECT *, ROW_NUMBER() OVER (Partition by CusID ORDER BY CusID) AS RowNumber
FROM MySchema.MyTable
)
select cusid,cusname,sum(rownumber) from example 
group by Cusid,Cusname
having .....

Upvotes: 3

Related Questions