question

gholamrezarezaie-0628 avatar image
0 Votes"
gholamrezarezaie-0628 asked gholamrezarezaie-0628 commented

last function in sql server

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 guiding131606-question-about-last-function.jpg


sql-server-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"Last" based on what criteria. Tables have no order, so you must order by something to get the "last" value.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered gholamrezarezaie-0628 commented

Hi @gholamrezarezaie-0628
131781-image.png

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.



image.png (17.1 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


131799-question-about-top1.jpg


0 Votes 0 ·
EchoLiu-msft avatar image EchoLiu-msft gholamrezarezaie-0628 ·

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.

0 Votes 0 ·

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





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

0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

LAST is not a valid Transact SQL command.

Please post table design as DDL, some sample data as DML statement and the expected result.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.