hello my friends . i need last fee of our buying. like this image i write script but my result is last' is not a recognized built-in function name . two table that you see in image is my tables that i use them for my main script. thanks alot for your guiding

Hi @gholamreza rezaie ， As Olaf said, the LAST function is not a built-in function of SQL Server,it is only supported in MS Access.In SQL Server, the LAST function can be replaced by the method mentioned by Erland. Applied to your code should look like this: SELECT TOP 1 Fee FROM i LEFT JOIN r ON r.condition=i.condition ORDER BY [DATE] DESC; If you have any question, please feel free to let me know. Regards Echo If the answer is helpful, please click " Accept Answer " and upvote it.

LAST is not a valid Transact SQL command. Please post table design as DDL, some sample data as DML statement and the expected result.

Maybe: SELECT TOP 1 Fee FROM ... ORDER BY Date DESC But this is quite much a guess on my part of what you are looking for.

last function in sql server

Accepted answer

EchoLiu-MSFT 14,571 Reputation points

2021-09-14T01:48:30.89+00:00
Hi @gholamreza rezaie ，

As Olaf said, the LAST function is not a built-in function of SQL Server,it is only supported in MS Access.In SQL Server, the LAST function can be replaced by the method mentioned by Erland.

Applied to your code should look like this:

SELECT TOP 1 Fee FROM i LEFT JOIN r ON r.condition=i.condition ORDER BY [DATE] DESC;

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
gholamreza rezaie 61 Reputation points

2021-09-14T07:32:17.973+00:00

thank a lot for your helping .. i do it but it result is just one fee of prodct.
we have a lot of product that we need last of all fee of products.
for example in this table it shows just fee of sony laptop and its true but i need all of fee of products

EchoLiu-MSFT 14,571 Reputation points

2021-09-14T07:46:34.89+00:00

If so, then we need to use another method.
Please try:

;WITH cte as(SELECT Fee,itemtitle, ROW_NUMBER() OVER(PARTITION BY itemtitle ORDER BY Fee DESC) rr FROM i LEFT JOIN r ON r.condition=i.condition) SELECT * FROM cte WHERE rr=1;

If this doesn't solve your problem,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result.

gholamreza rezaie 61 Reputation points

2021-09-14T09:32:07.85+00:00

hello thank you very much i do it and its very good .
i result with first select but second select has error

when i execute main select i t has error for where
i think i dont write corret.

select * from (SELECT Fee,itemtitle,

ROW_NUMBER() OVER(PARTITION BY itemtitle ORDER BY date DESC) rr
FROM INV.vwInventoryReceiptitem i
LEFT JOIN INV.vwInventoryReceipt r
ON InventoryReceiptID=i.InventoryReceiptRef ) WHERE rr=1

i think this is(my script) false

Erland Sommarskog 100.9K Reputation points MVP

2021-09-14T21:06:45.283+00:00

but second select has error

And what is that error?

gholamreza rezaie 61 Reputation points

2021-09-15T07:17:22.27+00:00

select * from (SELECT Fee,itemtitle,
ROW_NUMBER() OVER(PARTITION BY itemtitle ORDER BY date DESC) rr
FROM INV.vwInventoryReceiptitem i
LEFT JOIN INV.vwInventoryReceipt r
ON InventoryReceiptID=i.InventoryReceiptRef ) cte WHERE rr=1

thanks alot my friend i find my problem with your beautiful offer
Sign in to comment

last function in sql server

2 additional answers