1

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.

Community
  • 1
  • 1
avinashse
  • 1,440
  • 4
  • 30
  • 55
  • "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 Feb 07 '13 at 11:46
  • Also note that Sybase ASE does not support ROW_NUMBER() OVER. – Mike Gardner Feb 07 '13 at 15:28

2 Answers2

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

Gopal Sanodiya
  • 106
  • 3
  • 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. – avinashse Feb 07 '13 at 10:23
0

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

Gopal Sanodiya
  • 106
  • 3
  • 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. – avinashse Feb 08 '13 at 02:59