select query get the last but one ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select query get the last but one ID

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-06-23 : 14:27:15

Is it possible if i run this query, i get 5 ID's, out of that i want to get the last but one SUID.

select suid from tab_ccsnetsu where contractid=@contractid and cdrl=@CDRL and sutypeid=@SUTypeID order by suid

SUID results are: it should give me 33, which is the last but one.
2
7
17
33
45
----Here is a sample select with all values:
declare @TableSU table (SUID int)
insert @TableSU
select 2 union all
select 7 union all
select 17 union all
select 33 union all
select 45

select * from @TableSU
----------------------

thank you very much for the helpful info.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 14:43:41

Here are a couple ways:

SELECT TOP 1
    suid
FROM
(    
    select TOP 2
        suid 
    from 
        tab_ccsnetsu 
    where 
        contractid=@contractid 
        and cdrl=@CDRL 
        and sutypeid=@SUTypeID 
    order by suid DESC
) AS T
ORDER BY
    suid ASC    


-- Or
SELECT 
    suid
FROM
(    
    select
        suid,
        ROW_NUMBER() OVER (ORDER BY suid DESC) AS RowNum 
    from 
        tab_ccsnetsu 
    where 
        contractid=@contractid 
        and cdrl=@CDRL 
        and sutypeid=@SUTypeID 
) AS T
WHERE
    RowNum = 2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-23 : 14:43:58

Is it always the next to last suid as ordered?

;with SUIDS as
(
select 2 as SUID UNION
select 7 UNION
select 17 UNION
select 33 UNION
select 45
)

select suid
from
(

select suid
,row_number() OVER(order by suid desc) as row
from suids
) a
where row > 1

Jim

Everyday I learn something that somebody else already knew

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-06-23 : 15:07:11

It worked, Excellent thank you very much GURUS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources