How to Read the Last Row with SQL Server

How to read the last row with SQL Server

If you're using MS SQL, you can try:

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC 

How to select the last record of a table in SQL?

Without any further information, which Database etc the best we can do is something like

Sql Server

SELECT TOP 1 * FROM Table ORDER BY ID DESC

MySql

SELECT * FROM Table ORDER BY ID DESC LIMIT 1

How to read the last row with SQL

It is better to add one more column as Id or Date
then appy following query

select top 1 Name, Debit, Credit From aTable where Name=@Name order by Id desc

or

select top 1 Name, Debit, Credit From aTable where Name=@Name order by date desc

How to get the last row from a table in SQL Server?

Database tables are unsorted by nature. There is no last row in the table.

If there is a clustered index, The records are stored in the same order as the clustered index, but that does not mean that's the order of the rows - Unless you specify an order by clause in your query, no database guarantees the order of the rows returned by said query.

From Wikipedia page on tables (emphasis mine):

In terms of the relational model of databases, a table can be considered a convenient representation of a relation, but the two are not strictly equivalent. For instance, an SQL table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate tuples. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. However, the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified in the SELECT statement that queries the table.

See also Aaron Betrand's Bad habits to kick : relying on undocumented behavior, the paragraph titled "Ordering without an ORDER BY":

... I want to make it quite clear: ordering is arbitrary unless you use an ORDER BY clause. You should never, ever, ever rely on the ordering you observe in a query without an ORDER BY -- and you should only issue a query without an ORDER BY clause if you truly do not care what order the results come back. In such a case, you may as well imagine that the rows are going to come back in a different, random order each time, even though that is not truly the case (random has a meaning completely separate from arbitrary, but like I said, just imagine).

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

How to know last row when iterating cursor on SQL Server?

As suggested in the comments, putting commas between elements is far more easily realised by doing something special for the first row, rather than the last.^1,2

Rather than

WHILE @@FETCH_STATUS=0
BEGIN
    SET @value = @value + @newelement + ','

    ...
END

And trying to work out how to treat the last row specially, it's far easier to do:

SET @first = 1
WHILE @@FETCH_STATUS=0
BEGIN
    IF @first = 1
        SET @first = 0
    ELSE
        SET @value = @value + ','

    SET @value = @value + @newelement

    ...
END

---

An alternative transformation, if you really do need to do something for the last row is to use intermediate variables and a non-standard cursor loop:

declare boris cursor for select top 10 name from sys.objects order by name

declare @name sysname
declare @nametemp sysname

open boris
fetch next from boris into @nametemp
while @@FETCH_STATUS=0
BEGIN
    set @name = @nametemp --Use SELECT if multiple variables to be assigned

    fetch next from boris into @nametemp

    if @@FETCH_STATUS!=0
    begin
        print 'and finally'
    end

    print @name
END

close boris
deallocate boris

Which prints (for me, in a nearly empty DB):

EventNotificationErrorsQueue
filestream_tombstone_2073058421
filetable_updates_2105058535
IsIt
QueryNotificationErrorsQueue
queue_messages_1977058079
queue_messages_2009058193
queue_messages_2041058307
ServiceBrokerQueue
and finally
sysallocunits

But I'd strongly recommend the first type of transformation instead.

---

¹This isn't just a T-SQL thing either. In most programming languages, it's usually far easier if you can transform your problem from "treat the last iteration differently" into "treat the first iteration differently"

²And, of course, insert usual warnings about cursors being a last resort in SQL, use set-based approaches unless or until they're proved to be inadequate.

Select the first row and the last row in aggregation and create 2 columns in SQL

Here is one way to do this, using COUNT and ROW_NUMBER as analytic functions.

WITH cte AS (
    SELECT Customer_ID, Order_Date, Product_ID,
        COUNT(*) OVER (PARTITION BY Customer_ID) cnt,
        ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date) rn_first,
        ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date DESC) rn_last
    FROM Orders
)

SELECT
    Customer_ID,
    MAX(CASE WHEN rn_first = 1 THEN Product_ID END) AS Product_ID_first,
    MAX(CASE WHEN rn_last = 1  THEN Product_ID END) AS Product_ID_last
FROM cte
WHERE cnt >= 2
GROUP BY
    Customer_ID
ORDER BY
    Customer_ID;

Get the only the last row in a sequence. SQL Server

A simple way is a correlated subquery:

select t.*
from t
where t.seq = (select max(t2.seq) from t t2 where t2.prod = t.prod);

For performance, you want an index on (prod, seq).

The above often has the best performance. But another way to write the query is to use window fucntions:

select t.*
from (select t.*, row_number() over (partition by prod order by seq desc) as seqnum
      from t
     ) t
where seqnum = 1;

Select last record after selecting top N

You need to use your subquery as a derived table and select from that instead:

select top 1 *
from (select top 5 * 
      from tbl_dtr 
      where work_date < '2019/09/10' 
      order by Work_date asc) t
order by Work_date desc

---

Related Topics