Find the second most recent record (if it exists otherwise - the last record) in sql server
I have a table with a record like
Here I want Id 4 and 7 and 9
In this I want the details of every second (if it has a lot) entry. In the above table, I want to get the Id (4 for ticket 1001),
(7 for ticket 1002) and
(9 for ticket 1003).
for ticket 1003 it should get id 9 because it has no other id.
source to share
With sql-server 2005 and newer, you can 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 that looks like 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
source to share
Try the following:
generate a sequence number for your table and then apply the condition in the where clause.
i.e. where derived column name% 2 = 0
and please share your complete details what your data means and what exactly do you want from this input.
therefore, we will give you additional clarity about your problem and provide a solution.
Thank.
source to share