0

I'm trying to find the n-th row in a sybase database. I'm more familiar with SQL server so I decided to use a with statement but for some reason that's not working in sybase. Could you guys please explain what's wrong with this code:

With test AS 
(
  select *, row_number() over (order by M_MAT) as 'row'
  from OM_MAT_DBF
) 
SELECT *
FROM test
WHERE row = 2
user3284817
  • 61
  • 1
  • 2

3 Answers3

1

with and row_number() are not valid commands in Sybase ASE.

One option is to select your data (or key data) into a temp table, then use that to find the rows you are looking for.

set rowcount 13      --Use the row number you are looking for to limit rows returned
select rownumber=identity(10), M_MAT
  into #temp
  from OM_MAT_DBF
  order by M_MAT
set rowcount 0

This will create temporary table with rownumbers. Assuming M_MAT is a unique field:

select * 
  from OM_MAT_DBF
  where M_MAT =
    (
    select M_MAT 
    from #temp where rownumber = 13   --And find your requested row
    )

If you plan on selecting more than one row in a transaction, then you can always bypass the set rowcount command when creating the temp table.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
  • How exactly can I select the 3rd row for instance? I tried using your code but it only gives me the first row no matter if i change it from top 2 to top 40 – user3284817 Feb 07 '14 at 20:12
  • Hmm..I thought it worked, but now it appears I was mistaken. Looks like #temp tables is the way to go. Updating answer. – Mike Gardner Feb 10 '14 at 15:13
0

If its something like you want the nth row of a table based on some column value you can use the following option:

select *
from xyz X where 3 > (
    select count(*)
    from xyz
    where C1 > X.C1
)
order by C1

This will order the table based on column C1 and give you the 3rd row

Xint0
  • 5,221
  • 2
  • 27
  • 29
rinu
  • 37
  • 1
  • 7
0

Somehow, the answser from @Xint0 didn't work for me. I ended modifying it:

DROP TABLE #Data go

CREATE TABLE #Data (Datum INT, PRIMARY KEY (Datum))
INSERT INTO #Data VALUES (10)
INSERT INTO #Data VALUES (20)
INSERT INTO #Data VALUES (30)
INSERT INTO #Data VALUES (40)
INSERT INTO #Data VALUES (50)
INSERT INTO #Data VALUES (60)

DECLARE @n INT
SET @n = 4 -- find the n-th value

DECLARE @t INT
SELECT @t = count(*) - @n + 1 FROM #Data
SELECT TOP 1 *
FROM #Data X WHERE @t > (
 SELECT count(*)
 FROM #Data
 WHERE Datum > X.Datum
)
ORDER BY Datum
ArturoTena
  • 713
  • 5
  • 15