Finding Nth row in sql
substitute N for the row you want
select * from YOUR_TABLE t1
where (N-1) = (select count(distinct(t2.YOUR_COLUMN))
from YOUR_TABLE t2
and t2.YOUR_COLUMN > t1.YOUR_COLUMN)
This method is explained in detail here:
http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server
select * from YOUR_TABLE t1
where (N-1) = (select count(distinct(t2.YOUR_COLUMN))
from YOUR_TABLE t2
and t2.YOUR_COLUMN > t1.YOUR_COLUMN)
This method is explained in detail here:
http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server
posted by Jon B. at
9:39 AM
2 Comments:
this is best way:
Suppose:
table name is:MGT_AcademicSemesterName
attributes are:acSemId,academic
then find 4th row:
select
T1.RowNumber,T1.acSemId,T1.academicSemesterName
from
(
SELECT ROW_NUMBER() OVER (ORDER BY acSemId) AS RowNumber, acSemId,academicSemesterName
FROM MGT_AcademicSemesterName
) T1
where T1.RowNumber=4
re: find nth row in sql2000
suppose table name: TableName
Id: id1, id2, id3
select T1.id1 from
(
select
(select count(*) from TableName as e2 where e2.id1 <= e1.id1)
as myNo, e1.id1
from TableName as e1) T1
where T1.myNo=4
Post a Comment
<< Home