Solved

# returning table row at specific position (e.g. first, second third)

Posted on 2007-08-09
441 Views
Hi

How do i return the nth row in a an ordered table where the row matches a partical criterion

e.g. select nth record from table where column = value

The table is already ordered to support this query. I only really need the first and second rows

thanks a lot
andrea
0
Question by:andieje

LVL 42

Expert Comment

>The table is already ordered to support this query

Huh? Anyway, doesn't matter--you need to order it.

Select top 2 * from yourtable where column=value
ORDER BY colA, colB
0

Author Comment

I obviously didnt make my query clear

I need the query to return one record only. For example i need the query to return the first record or i need the query to return the second record

thanks
0

LVL 42

Accepted Solution

Assume your table is "ordered" by colA in ascending sequence.

Two ways to skin the cat:

Select T3.* from
(
Select T1.*
, (select count(T2.*) from YourTable as T2
where T2.colA <= T1.colA
) as RowNum
from YourTable as T1
) as T3
where t3.rownum = 2

Select T1.* from YourTable as T1
where
(select count(T2.*) from YourTable as T2
where T2.colA <= T1.colA) = 2

Of course it gets a bit messier if you have ties in the order. Let me know.
0

LVL 3

Assisted Solution

Here a speedy query. Let's assume that YourTable is ordered by Col1. The query below will return the 2nd row.

SELECT TOP 1 *
FROM
(
SELECT TOP 2 * -- change the "2" here to specify the "nth" row you need
FROM YourTable
ORDER BY Col1
) TABLE_A
ORDER BY Col1 DESC
0

## Featured Post

### Suggested Solutions

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.