|
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 |
|
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 I use sybase ASE and it doesn't allow to use TOP in subquery
(25 Jan '13, 12:22)
tunguyen825
Replies hidden
In this case 1. change the forum, 2. migrate to SQLA it is the much cooler database system ;-)
(25 Jan '13, 12:24)
Martin
yes, I double checked
(25 Jan '13, 12:39)
Martin
1
FWIW, just two hints to think over ASE vs. SQL Anywhere:
(27 Jan '13, 12:27)
Volker Barth
|
Sure. Please show the query (or at least a part of it) - that makes suggestions much easier...
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:
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.)
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.