-1

I have a MS Access database and I am working on one of its table. When I use the select query order by date, it gives 1000 approx rows. I want to display the 3rd row only. How can I use the select query that provides me with an output of 3rd row only?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Andy
  • 75
  • 1
  • 10

2 Answers2

3

nested query would do it, something like this:

declare @table table (id int)
insert into @table values (1),(2),(3),(4),(5)

select top 1 id from
(
    select top 3 id from @table
    order by id desc
) t
order by t.id asc

Edit: Just noticed you said it was MS-Access, The select query would still be the same

dbajtr
  • 2,024
  • 2
  • 14
  • 22
  • Thanks, worked for me – Andy Jul 11 '17 at 11:33
  • No worries, give it a tick if helpful :) – dbajtr Jul 11 '17 at 12:04
  • 1
    @dbajtr Literally thank you so much I was trying to find a solution for .accdb instead of limit like I'm used to and this works perfectly. Finally got a solution to a problem that was bugging me for 2 hours. – Riley Carney Nov 06 '18 at 23:59
  • 1
    Hiya, I've tried this solution in MS Access and it doesn't work - it finds the last n rows from the table, and then returns the top result from that. So in this example, it would return the third to last result, whereas I want the third result. Any idea how to fix? – Lou Dec 12 '19 at 12:02
  • Reversing the "asc" and "desc" should do the trick – CSG Jun 22 '21 at 01:08
0

What exactly do you need it for? May be it is reasonable to use some Vba and Dao.Recordset techniques to show the result?

            Sub ShowRow()

            Dim rs As Dao.Recordset
            Dim intFields As Integer
            Dim i As Integer
            Dim lng As Long
            Dim str As String

            Set rs = openrecordset("tbl")
            rs.movelast
            rs.movefirst

            intFields = rs.Fields.Count

            For lng = 1 To 10
              rs.movenext
            Next lng


            For i = 1 To intFields
              str = str & rs(1).Value
            Next i

            str = Trim(str)
            MsgBox str

            End Sub
N.Russ
  • 1
  • 2