sybase sql for paging

[Former Member]

We use TOP command and ORDER_BY to implement paging for our application with Sybase database: select TOP 100 id, name from tableA order by id The problem is when add join tables, we start to receive duplicate "id" and hence the total record is less than expected (because one row on the main table can match many rows in child table). Anybody has a solution for this?

My query:

select TOP 100 a.id, a.name, b.name, c.name
  from tableA a 
  left outer join tableB b on a.id = b.id 
  left outer join tableC c on a.id = c.id 
 where a.id < pass_in_id order by a.id desc

[VolkerBarth]

Sure. Please show the query (or at least a part of it) - that makes suggestions much easier...

[VolkerBarth]

Well, if there are duplicate rows in the result set (that's my understanding of your question, but I'm guessing), and you want to eliminate them, use the DISTINCT keyword, such as:

select distinct TOP 100 a.id, a.name, b.name, c.name

Note, that may slow down your query, so you it only if necessary.

(FWIW, "GROUP BY a.id, a.name, b.name, c.name" would be an alternative.)

[Former Member]

when I use DISTINCT in select, it still has duplicate on the id such as:

row/a.id/a.name/b.name/c.name

1/100/joe/attribute1/test

2/100/joe/attribute2/test

i think distinct is for every columns in the select statements.

[MCMartin]

Probably not elegant, but seems to be what you are looking for:

select ... where a.id in (select top 100 a.id from a where a.id<pass_in_id oder by a.id desc)

Fill your select part into the "..."

Anyway based on your joins this can result in more than 100 rows...but all first 100 ids will be included

[Former Member]

I use sybase ASE and it doesn't allow to use TOP in subquery

[MCMartin]

In this case 1. change the forum, 2. migrate to SQLA it is the much cooler database system 😉

[Former Member]

option 2 sounds good. Does SQLA allow order by in subquery also?

[MCMartin]

yes, I double checked

[VolkerBarth]

FWIW, just two hints to think over ASE vs. SQL Anywhere:

• From Breck's blog (an old article, but still true IMHO): Today's Client Question: SQL Anywhere vs. ASE
• Migrating SQL Anywhere Database Applications to Sybase ASE - which does contain an advanced comparison of both products.