CODEWITHSUNDEEP
sqlsql-server
Letoncse
Letoncse

Reputation: 722

How to find the Second value from a table.?

I have One table with StatusID in StatusHistory Table. One customer could be multiple statusID. I need to find just previous statusID that mean the second Status ID which just befor he was hold.

I am getting current one this bellow way:

SELECT top 1 StatusIDHeld
FROM dbo.UserStatusHistory
WHERE userid=2154
ORDER BY tatusChangedOn DESC

Question:

I need 2nd statusID means just previous statusID

How to find the Second value(StatusID) from a table.?

Upvotes: 0

Views: 153

Answers (4)

Maciej Los
Maciej Los

Reputation: 8591

There's nothing like second value of table. It depends on many factors, like indexes, etc.

To be able to get 1., 2. or n-th record depending on sort order, use ROW_NUMBER() function.

SELECT StatusIDHeld 
FROM
(
    SELECT StatusIDHeld, ROW_NUMBER () OVER(ORDER by StatusIDHeld) as RowNo
    FROM UserStatusHistory
) AS t
where t.RowNo = 2

Another way is to use TOP instruction twice:

SELECT TOP(1) StatusIDHeld
FROM (
    SELECT TOP(2) StatusIDHeld
    FROM UserStatusHistory
    WHERE userid=2154
    ORDER BY tatusChangedOn ASC
) AS t
ORDER BY StatusIDHeld DESC

Upvotes: 1

James Z
James Z

Reputation: 12317

One way to do this is to fetch the first 2, and then take the second one of them:

select top 1 StatusIDHeld from (
  select top 2 StatusIDHeld, StatusChangedOn 
  from dbo.UserStatusHistory 
  order by StatusChangedOn DESC
) TMP order by StatusChangedOn ASC

Upvotes: 1

Abhishek Mittal
Abhishek Mittal

Reputation: 366

You need to offset your query reset by 1.

Please try this:

select StatusIDHeld from dbo.UserStatusHistory order by tatusChangedOn DESC Limit 1 OFFSET 1;

Upvotes: 0

ASh
ASh

Reputation: 35681

select StatusIDHeld 
from
(select 
    StatusIDHeld,
    ROW_NUMBER () over (order by tatusChangedOn DESC) as num
from dbo.UserStatusHistory
where userid=2154
) T
where T.num = 2

Upvotes: 4

Related Questions