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

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
andiejeAsked:
Who is Participating?
 
dqmqCommented:
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
 
dqmqCommented:
>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
 
andiejeAuthor Commented:
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
 
monvelasquezCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.