How to Return Second Newest Record in SQL

How to return second newest record in SQL?

SELECT * 
FROM Table
WHERE Date = ( SELECT MAX(Date) 
               FROM Table
               WHERE Date < ( SELECT MAX(Date) 
                              FROM Table
                            )
             ) ;

or:

SELECT TOP (1) * 
FROM Table
WHERE Date < ( SELECT MAX(Date) 
               FROM Table
             ) 
ORDER BY Date DESC ;

or:

SELECT *
FROM
  ( SELECT t.*
         , ROW_NUMBER() OVER(ORDER BY Date DESC) AS RowNumber
    FROM Table t
  ) AS tmp
WHERE RowNumber = 2 ;

---

If the Date column has unique values, all three queries will give the same result. If the column can have duplicate dates, then they may give different results (when there are ties in 1st or 2nd place). The first query will even give multiple rows in the result if there are ties in 2nd place.

How to find second last record from the table using SQL query?

Use below query:

 select top 1 * from [Your_Table_Name] where [Your_Table_Primary_Name] in
     (select top 3 [Your_Table_Primary_Name] from [Your_Table_Name] order by
      [Your_Table_Primary_Name] desc) 
order by [Your_Table_Primary_Name] asc

Description

---

1. first you need to select last three records using bellow syntax

 select top 3 [Your_Table_Primary_Name] 
 from [Your_Table_Name] 
 order by [Your_Table_Primary_Name] desc

2. after then you need to select first record using bellow syntax

 select top 1 * 
 from [Your_Table_Name] 
 where [Your_Table_Primary_Name] in (***First Syntax/Above described Syntax*****) 
 order by [Your_Table_Primary_Name] asc

Taking the second last row with only one select in SQL Server?

To get the 2nd last row in one select:

SELECT TOP 1 * From
(select Top 2 * from Cinema ORDER BY CinemaID DESC) x                     
ORDER BY CinemaID

It's really only "one" select because the outer select is over only 2 rows.

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

How can I retrieve second last row?

As you asked I can give you example.

Imagine, that you have full bag of apples. How can you take second last apple? How you will know which one is second last? You can't do It while you not sort them in any way.

---

For now your data isn't sorted so you can't achieve It as expected. You can do It in following, only after you have any sorting criteria like Id, date created or etc.

SELECT TOP 1 * 
FROM(
    SELECT TOP 2 * 
    FROM Tbl 
    ORDER BY SortingCol DESC -- here you need to pass column which will provide expected sorting
    ) t                     
ORDER BY SortingCol

Selecting most recent and specific version in each group of records, for multiple groups

To get only latest revisions:

SELECT * from t t1
WHERE t1.rev = 
  (SELECT max(rev) FROM t t2 WHERE t2.id = t1.id)

To get a specific revision, in this case 1 (and if an item doesn't have the revision yet the next smallest revision):

SELECT * from foo t1
WHERE t1.rev = 
  (SELECT max(rev) 
   FROM foo t2 
   WHERE t2.id = t1.id
   AND t2.rev <= 1)

It might not be the most efficient way to do this, but right now I cannot figure a better way to do this.

how do I query sql for a latest record date for each user

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

how to get second last record from driverid Sql server

Use Row_Number() Window function

With cte as
(
select *,Row_Number()over(order by Transdate desc) as Rn from yourtable 
Where DriverID = 134
)
Select * 
from cte 
Where Rn = 2

To handle when there is only one record for the give DriverID then use this

With cte as
(
select *,Row_Number()over(order by Transdate desc) as Rn,
count(1)over() as cnt from yourtable 
Where DriverID = 134
)
Select * 
from cte 
Where (Rn = 2 and cnt > 1) or (Rn = 1 and cnt = 1)

Note: If there is a tie in last second record and you want both the records then use DENSE_RANK instead of ROW_NUMBER

Update :

To find the second last record for all DriverID's add Partition by in Row_number and remove the DriverID from Where clause

With cte as
(
select *,Row_Number()over(Partition by DriverID order by Transdate desc) as Rn,
count(1)over(Partition by DriverID) as cnt from yourtable 
)
Select * 
from cte 
Where (Rn = 2 and cnt > 1) or (Rn = 1 and cnt = 1)

---

Related Topics