How can i find the fifth record in a table using sql query?
+1
A:
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM Table T ORDER BY Column ASC) ORDER BY Column Desc
Itay
2010-10-22 10:53:50
+1
A:
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;
il_guru
2010-10-22 10:54:58
can u explain a bit how the sqlservver query is working
NoviceToDotNet
2010-10-22 11:15:05
I edited because i left the field name i used for test (utente) instead of "orderingColumn". I think now should be clearer. The subquery retrieves the first 5 rows ordered by the desired column (our desired row is the last one in this set) and then reverse the ordering of this subset and take the first row, which is the one we are looking for.
il_guru
2010-10-22 12:55:57
+1
A:
Fifth record only in MySQL
SELECT * FROM anytable LIMIT ORDER BY id LIMIT 4,1
Ed.C
2010-10-22 11:01:14
+9
A:
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!"
Thomas L Holaday
2010-10-22 11:11:12
A:
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 :)
Matt Gibson
2010-10-22 13:03:28