SQL Server Select Last N Rows

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 SELECT the last 10 rows of an SQL table which has no ID field?

SQL tables have no implicit ordering, the order has to come from the data.
Perhaps you should add a field to your table (e.g. an int counter) and re-import the data.

However that will only give the order of the import and not the data. If your data has no ordering you have to find out how to add it.

EDIT: you say

...to make sure it imported everything.

What's wrong with using row count?

SQL where last n records for each id meet a certain criteria

Based on the requirement of last 5 or more records check. If we have continuous rows for 'AAA' and then 'CCC' or anything other, we need to make sure that we are comparing the table to make sure that there is no other values.

     with cte as (
select 1 as ID, '2019-02' as Date, 'AAA' as Value union all
select 1 as ID, '2019-03' as Date, 'AAA' as Value union all
select 1 as ID, '2019-04' as Date, 'AAA' as Value union all
select 1 as ID, '2019-01' as Date, 'BBB' as Value union all
select 1 as ID, '2019-06' as Date, 'CCC' as Value union all
select 1 as ID, '2019-07' as Date, 'AAA' as Value union all
select 1 as ID, '2019-08' as Date, 'AAA' as Value union all
select 1 as ID, '2019-09' as Date, 'AAA' as Value union all
select 1 as ID, '2019-10' as Date, 'AAA' as Value union all
select 1 as ID, '2019-11' as Date, 'CCC' as Value union all
select 2 as ID, '2019-05' as Date, 'AAA' as Value union all
select 2 as ID, '2019-07' as Date, 'BBB' as Value union all
select 2 as ID, '2019-03' as Date, 'AAA' as Value union all
select 2 as ID, '2019-08' as Date, 'AAA' as Value union all
select 2 as ID, '2019-09' as Date, 'AAA' as Value union all
select 2 as ID, '2019-10' as Date, 'AAA' as Value union all
select 2 as ID, '2019-11' as Date, 'AAA' as Value union all
select 2 as ID, '2019-12' as Date, 'AAA' as Value union all
select 3 as ID, '2019-02' as Date, 'CCC' as Value union all
select 3 as ID, '2019-03' as Date, 'AAA' as Value union all
select 3 as ID, '2019-02' as Date, 'BBB' as Value union all
select 3 as ID, '2019-04' as Date, 'AAA' as Value union all
select 3 as ID, '2019-05' as Date, 'AAA' as Value union all
select 3 as ID, '2019-06' as Date, 'AAA' as Value union all
select 3 as ID, '2019-07' as Date, 'AAA' as Value union all
select 3 as ID, '2019-08' as Date, 'AAA' as Value 
 ) 





    select ID  
 from  cte  z
  where not exists (Select 1 from cte c where z.id = c.id and z.Date < c.Date and c.Value <> 'AAA')
 group by ID  
 having count(case when Value = 'AAA' then 1 end  ) >=5  



Output:
ID 

3 

sql select last n rows, sort them reversed

Use a subquery:

SELECT ce.*
FROM (SELECT fecha_hora, estado_viejo
      FROM cambios_de_estado 
      WHERE usuario_id= '35512'
      ORDER BY fecha_hora DESC
      LIMIT 9
     ) ce
ORDER BY fecha_hora ASC

How to get last N records after JOIN in SQL?

Put the limit in a subquery that fetches the 3 latest notes before joining.

SELECT N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time 
from (SELECT *
      FROM notes
      WHERE user_id = 2
      ORDER BY date DESC
      LIMIT 3) AS N 
JOIN notes_subject AS S ON N.user_id = S.user_id 
                       AND N.note_id = S.note_id 
JOIN notes_tag AS T ON N.user_id = T.user_id 
                   AND N.note_id = T.note_id 

---

Related Topics