[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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
0
andieje
Asked:
andieje
  • 2
2 Solutions
 
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
 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now