select nth row from a table sybase

Question

I am using sybase database.
I have to select every nth row from my table selfjoin(id,salary);

I use

select top 1 * from (select top 4 * from selfjoin order by id desc) order by id

I get an error though.

An ORDER BY clause is not allowed in a derived table.

The below sql also results in an error

select id from selfjoin order by id asc limit 2 --error :-`Incorrect syntax near 'limit'`

Also the below sql throws an error.

SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS rownumber,salary from selfjoin; --error :- `Incorrect syntax near the keyword 'OVER'.`

I also read this link but no query is working. I also checked for this page,but didn't get correct result.

Change in Question:- salary in table is in ascending order. i.e., nth row is to be found according to ascending order of salary.

"Sybase" is not a product, it's a company offering at least four different RDBMS products - Adaptive Server Enterprise, Adaptive Server IQ, SQL Anywhere, and Advantage Database Server. And that's ignoring the SAP products. Please be specific as to which product you're talking about, and which version. — Graeme Perrow
– Graeme Perrow, Commented Feb 7, 2013 at 11:46
Also note that Sybase ASE does not support ROW_NUMBER() OVER. — Mike Gardner
– Mike Gardner, Commented Feb 7, 2013 at 15:28

Gopal Sanodiya · Accepted Answer · 2013-02-07 09:56:45Z

0

Check out the below Query:-

SELECT * from selfjoin s1 where (n-1) =(select count(id) from selfjoin s2 where s1.id>s2.id)

where n is rownumber

answered Feb 7, 2013 at 9:56

Gopal Sanodiya

1063 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

avinashse Over a year ago

This query will give you the nth max id from the table, and you must use count(distincct id) in order to give the correct result.. Apart from this, for e.g., if n=3 then i should get 3rd row,6th row,9th row...i.e, every nth row.

Gopal Sanodiya · Accepted Answer · 2013-02-07 13:49:52Z

well, if id is some sequential incremental number then you can do something like below:-

create table #tmp2(id numeric identity,name char(9)) insert into #tmp2 values("B") insert into #tmp2 values("C") insert into #tmp2 values("D") insert into #tmp2 values("E") insert into #tmp2 values("F") insert into #tmp2 values("G") insert into #tmp2 values("H") insert into #tmp2 values("I") insert into #tmp2 values("J") insert into #tmp2 values("K") insert into #tmp2 values("L") select t1.* from #tmp2 t1,#tmp2 t2 where t1.id=t2.id*2 ---(nth number)

or if id is not start from 1 then

select t1.* from #tmp2 t1,#tmp2 t2 where t1.id=((t1.id+1)-t2.id)*2 ---(nth number)

result:-

id name
2 C
4 E
6 G
8 I
10 K

You must read the question carefully.. i told salary is in ascending order. not the identity. for e.g., #tmp(id,name)={{1,A},{2,P},{3,D},{4,Z},{5,C}} then table is arranged order by name not id i.e, {{1,A},{5,C},{3,D},{2,P},{4,Z}} so if n=2 then correct result will be :- {{5,C},{2,P}} I hope you have understood the question now, and one more point. You cant alter the table for adding new identity column.

Collectives™ on Stack Overflow

select nth row from a table sybase

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related