CODEWITHSUNDEEP
sqlsql-server
Subo
Subo

Reputation: 445

Find 2nd last record(if available otherwise the last record) in sql server

I have a table with record like

enter image description here

Here I want the Id 4 and 7 and 9

In this I want the details of every 2nd (if it have many) record . In the above table I want to fetch Id
(4 for ticketid 1001),
(7 for ticketid 1002) and
(9 for ticketid 1003) .
for ticketid 1003 it should fetch id 9 because it have no other id.

Upvotes: 3

Views: 54

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460038

With sql-server 2005 and newer you could use this approach:

WITH CTE AS
(
    SELECT ID, Status, TicketID,
           RN = ROW_NUMBER() OVER (PARTITION BY TicketID ORDER BY ID DESC),
           CNT = COUNT(*) OVER (PARTITION BY TicketID)
    FROM dbo.TableName t
)
SELECT ID, Status, TicketID
FROM CTE
WHERE CNT = 1 OR RN = 2

If you don't want to use a common-table-expression which is similar to a subquery/view:

SELECT x.ID, x.Status, x.TicketID
FROM ( SELECT ID, Status, TicketID,
              RN = ROW_NUMBER() OVER (PARTITION BY TicketID ORDER BY ID DESC),
              CNT = COUNT(*) OVER (PARTITION BY TicketID)
       FROM dbo.TableName t ) x
WHERE x.CNT = 1 OR x.RN = 2

Upvotes: 4

Phani
Phani

Reputation: 93

Try with this following,

generate a sequence number for your table and then in apply a condition in where clause.

i.e. where derived column name %2 = 0

and please share your full information means what is your data and what you exactly want from that input.

so we will give get some more clarity on your problem and will provide the solution.

Thanks.

Upvotes: 0

Related Questions