Select all rows except last one

Question

I have a select query where I have RowNumber() function, so I have row number by a partition in the result.

What do I want to achieve? I want to update all row numbers of each partition except the last row of each partition. So suppose we have RowNumber 1,2,3,4 and another partition with 1,2 I want to do an update clause to update all row numbers except number 4(first partition) and exclude row number 2 of the second partition.

I know we can exclude first row number using a simple condition in where clause like RowNumber != 1 but in this case, I want the last row number of each partition and that number is different in all partitions .

How can I achieve this? Regards

This was in the solution to your [previous question](https://stackoverflow.com/questions/60730698/get-last-row-of-each-group-with-cte). — SMor, Jun 17 '20 at 13:46
Generic version of this question: [SQL Selecting all BUT the last row in a table?](https://stackoverflow.com/q/19470230/11107541) — starball, Jun 08 '23 at 00:43

score 1 · Answer 1 · answered Jun 16 '20 at 17:57

1

You can use an updatable CTE, but reverse the direction of the order by in row_number():

with toupdate as (
      < your query here but with DESC sort instead of ASC sort or vice versa >
     )
update toupdate
    set . . .
    where rownumber = 1;

answered Jun 16 '20 at 17:57

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jun 16 '20 at 18:17

0

Since you have not provided any SQL. As per your question i can think of putting your result in a subquery and using the max of RowNumber. Or better provide some sql and sample data for better clarity.

answered Jun 16 '20 at 18:17

Mahesh

75
1
1
9

score 0 · Answer 3 · answered Jun 16 '20 at 18:23

0

You can just change the ORDER BY clause from ASC to DESC or DESC to ASC as per the requirement and you can continue updating as

Where RowNumber != 1

answered Jun 16 '20 at 18:23

Shushil Bohara

5,556
2
15
32

score 0 · Answer 4 · answered Jun 16 '20 at 18:53

0

This would work too:

  with temptbl as (
     select *,max(rn) over (partition by id order by somethingelse) rnmax from dbo.yourtable
     )
update temptbl
    set [whatever you plan on updating]
    where rn=rnmax;

answered Jun 16 '20 at 18:53

SQLserving

380
1
4
16

score 0 · Answer 5 · answered Jun 17 '20 at 00:49

As many other suggested, turn the order on its head would make it much simpler as row 1 would be the row you don't want to update.

Just make sure to use

....
,row_number() over(partition by TEMP1 order by TEMP2 desc[or asc])
....

where rn <> 1

and you should be on the right path.

Select all rows except last one

5 Answers5