Query for select 2nd row of a column

Question

I have an SQL statement that yeilds something like this:

Amounts
$101.45
$1000.56
$20978.44
$2.98

The SQL that got me this is:

select CHANGE_EFFECTIVE_AMOUNT
from V_Rpt
where ACCOUNT_NUMBER = '100'
and CHANGE_TYPE_CODE = 2
order by TRAN_SEQUENCE_NUMBER desc

How can I structure my statement so that it only returns the second value in this column? Basically, something like "select the 2nd in a column from..." The value will change all the time but it will always be the value after the top one.

I am using Microsoft SQL Server 2008 R2, in case it matters. And, this will eventually go into MS Access 2007 as a pass-through query.

Your question is unclear. Please clarify by providing more data and expected result. Also, what determines 2nd? Does your query only return rows of dates? — Felix Pamittan, Feb 27 '15 at 00:51
Unless you `order by` something, there is no inherent notion of row ordering. Your best bet would be to use a `rank` function of some kind. — , Feb 27 '15 at 00:51
possible duplicate of [LIMIT 10..20 in SQL Server](http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server) — Steve Robbins, Feb 27 '15 at 00:57
I tried `ROW_NUMBER()` and it did create a column that identified my value as 2, but when I added a where clause to say `and RowNumber = 2`, it gave me an error that I couldn't figure out. — msim, Feb 27 '15 at 00:59

Felix Pamittan · Accepted Answer · 2015-02-27T01:05:21.607

1

You could achieve this using the ROW_NUMBER() function.

;WITH CTE AS(
    SELECT 
        CHANGE_EFFECTIVE_AMOUNT,
        RN = ROW_NUMBER() OVER(ORDER BY TRAN_SEQUENCE_NUMBER DESC)
    FROM V_Rpt
    WHERE 
        ACCOUNT_NUMBER = '100'
        AND CHANGE_TYPE_CODE = 2
)
SELECT CHANGE_EFFECTIVE_AMOUNT
FROM CTE
WHERE RN = 2

This will return nothing if your query has only 1 row.

Using a SUBQUERY:

SELECT
    t.CHANGE_EFFECTIVE_AMOUNT
FROM(
    SELECT 
        CHANGE_EFFECTIVE_AMOUNT,
        RN = ROW_NUMBER() OVER(ORDER BY TRAN_SEQUENCE_NUMBER DESC)
    FROM V_Rpt
    WHERE 
        ACCOUNT_NUMBER = '100'
        AND CHANGE_TYPE_CODE = 2
)t
WHERE t.RN = 2

edited Feb 27 '15 at 01:05

answered Feb 27 '15 at 00:59

Felix Pamittan

31,544
7
41
67

thank you very much, I am trying to learn about CTEs. I tried what you did and got this error: `The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.` – msim Feb 27 '15 at 01:01
oh awesome that worked!! Do you happen to know if CTEs are allowed in pass-through queries in MS Access? – msim Feb 27 '15 at 01:03
Oh, that I dont' know. You could easily convert it a subquery. See updated answer. – Felix Pamittan Feb 27 '15 at 01:05
that's killer, thank you for such a swift and helpful response. I will learn more about CTEs. Thanks!! – msim Feb 27 '15 at 01:10

score 0 · Answer 2 · answered Feb 27 '15 at 01:04

0

Try to add this OFFSET 1 ROW FETCH NEXT 1 ROW ONLY to your query.

  select CHANGE_EFFECTIVE_AMOUNT
    from V_Rpt
    where ACCOUNT_NUMBER = '100'
    and CHANGE_TYPE_CODE = 2
    order by TRAN_SEQUENCE_NUMBER desc OFFSET 1 ROW
    FETCH NEXT 1 ROW ONLY;

OFFSET skip 1 row and return only the next row from the result set.

answered Feb 27 '15 at 01:04

Nin-ya

252
1
12

the answer above may have taken care of everything but just so you know, this returned an error `Incorrect syntax near 'OFFSET'` and `Invalid usage of the option NEXT in the FETCH statement`. – msim Feb 27 '15 at 01:07
1

Because that's SQL2012 syntax. The OP asked about SQL2008R2 – Kirill Slatin Feb 27 '15 at 01:24

score 0 · Answer 3 · edited Feb 27 '15 at 03:34

0

select * from table_name limit 1,1

For limit x,y x is index of start row and y is number of lines.

edited Feb 27 '15 at 03:34

eebbesen

5,070
8
48
70

answered Feb 27 '15 at 01:08

Gilson

478
7
14

select CHANGE_EFFECTIVE_AMOUNT from V_Rpt where ACCOUNT_NUMBER = '100' and CHANGE_TYPE_CODE = 2 order by TRAN_SEQUENCE_NUMBER desc limit 1,1 – Gilson Feb 27 '15 at 01:12
2

`Limit` clause is not part of standard SQL nor SQL-SERVER. Even SQL2012 doesn't have this syntax. Use [OFFSET FETCH](https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx) instead in SQL2012 – Kirill Slatin Feb 27 '15 at 01:24
i was asking myself why the answer is so complicated. I only worked with Mysql and Oracle :p – Gilson Feb 27 '15 at 01:37

Query for select 2nd row of a column

3 Answers3