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?
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
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