CODEWITHSUNDEEP
sql
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

sql query to find fifth record

How can i find the fifth record in a table using sql query?

Upvotes: 3

Views: 3034

Answers (6)

Matt Gibson
Matt Gibson

Reputation: 38238

For SQL Server (recent-ish incarnations, anyway) something like this should work:

SELECT
  *
FROM
  (
    SELECT 
      *, 
      ROW_NUMBER() OVER (ORDER BY the_table.the_column) 'row_num'
    FROM 
      the_table
  ) numbered_rows
WHERE
  row_num = 5

However, I'd actually put my vote with Thomas L Holaday's answer :)

Upvotes: 0

il_guru
il_guru

Reputation: 8508

If you are using SqlServer you could use the TOP keyword to achieve this.

select top 1 * from(
select top 5 * from myTable order by orderingColumn) as A
order by orderingColumn desc

If you are using Oracle this should work (however i am not able to test this now)

select *
  from (
    select *, row_number() over (order by orderingColumn) r
      from items
  )
where r = 5;

Upvotes: 2

Thomas L Holaday
Thomas L Holaday

Reputation: 13814

If you are feeling argumentative, consider using "SELECT * FROM table LIMIT 1" and arguing that since SQL does not promise to return results in any particular order, the row returned is spiritually equivalent to the fifth, then show your tattoo: "The nth element of an unordered set is meaningless!"

Upvotes: 10

Ed.C
Ed.C

Reputation: 798

Fifth record only in MySQL

SELECT * FROM anytable LIMIT ORDER BY id LIMIT 4,1

Upvotes: 1

Ton van den Heuvel
Ton van den Heuvel

Reputation: 10528

SELECT * FROM table LIMIT 1 OFFSET 4;

Upvotes: 1

Itay Karo
Itay Karo

Reputation: 18286

SELECT TOP 1 * FROM (SELECT TOP 5 * FROM Table T ORDER BY Column ASC) ORDER BY Column Desc

Upvotes: 2

Related Questions