How to get last row

1

i have table contain
ItemID Quantity
125 2.3
125 2.6
125 1.3
129 5.3
129 6.3
129 3.2

i need to get last row PARTITION BY ItemID
ItemID Quantity
125 1.3
129 3.2

2

There is no order to the data in a table. So to get the "last" row, you have to define the sorting order. For your sample data, you can get the result you want via the following query. However, the sorting order I used simply picks the row with the lowest quantity. If that is not the sorting order you want, is there some other column in the table that will tell you which row is the last?

;WITH cte AS
(
   SELECT ItemId, Quantity,
      ROW_NUMBER() OVER(PARTITION BY ItemId ORDER BY Quantity) AS RN
   FROM
      YourTable
)
SELECT ItemId, Quantity
FROM cte
WHERE RN=1;
1 Like
3

thank you JamesK
But I need Used Last_Value function
but this function is duplicated Last vlue

4

The last_value function also requires a sort order (order by), so @JamesK is not asking you for this, just to annoy you or be funny.

You could do:

select distinct
       itemid
      ,last_value(quantity) over(partition by itemid order by (select null))
  from yourtable
;

but sql engine doesn't promise the sort order (unless you specify it).

Lets say you have you sample data you provided

create table yourtable (
   itemid int
  ,quantity decimal(12,2)
);

insert into yourtable (itemid,quantity)
 values (125,2.3)
       ,(125,2.6)
       ,(125,1.3)
       ,(129,5.3)
       ,(129,6.3)
       ,(129,3.2)
;

If you do a "select *" without "order by", you probably will get the rows in the order it was inserted (but sql doesn't promise you will).

Now you do a little insert/delete/update

update yourtable
   set quantity=10.10
 where itemid=125
   and quantity=1.3
;

delete from yourtable
 where itemid=125
   and quantity=2.6
;

insert into yourtable (itemid,quantity)
 values (125,20.20)
;

Using normal human logic, the "latest" row for itemid 125 would be the last insert (quantity 20.20), but sql doesn't garantee that.
On my test system, the "latest" row for itemid 125 is quantity 10.10, probably because mssql engine reused the space where I deleted quantity 2.6, and put the 20.20 row there instead.

See the problem?

ps.: Sorry for "butting in" @JamesK

3 Likes
5

Thank you for expressing what I was trying to say much better than I did, @bitsmed. You are not "butting in" at all :slight_smile: