CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Turtleman10
Turtleman10

Reputation: 119

getting second to last record per group in SQL

I know how to get the last record of a group but I also need to get the second to last record. How can I do this? here is my code for getting the last record.

select job qjob, max(id) qid from sqbclog group by job

Upvotes: 1

Views: 5220

Answers (4)

M.Ali
M.Ali

Reputation: 69584

SELECT * 
FROM (
select job AS qjob
      , id AS qid
      ,ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY ID DESC) AS RN
from sqbclog
)Sub
WHERE rn <= 2

This query will return last two records but if you only need the 2nd last record then in where clause use where en = 2 else leave it as it is.

Upvotes: 5

Scott
Scott

Reputation: 19

select job qjob, max(id) -1 qid 
from sqbclog
group by job

Upvotes: 0

DavidN
DavidN

Reputation: 5207

If I've understood your original query correctly, and it does in fact give you the last row, then TOP 2 should give you the last 2 rows:

SELECT TOP 2 job qjob, max(id) qid from sqbclog group by job

Upvotes: 0

Up_One
Up_One

Reputation: 5271

This should do it ! 

select * 
    from sqbclog 
    where id not in (
        select top (
            (select count(*) from sqbclog ) - 2
        ) id 
        from sqbclog 
    )

Upvotes: 0

Related Questions