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

asked 25 Jan '13, 11:00

tunguyen825's gravatar image

tunguyen825
15113
accept rate: 0%

edited 26 Jan '13, 17:39

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

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

(25 Jan '13, 11:19) Volker Barth

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.)

(25 Jan '13, 12:02) Volker Barth
Replies hidden

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.

(25 Jan '13, 12:15) tunguyen825

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

permanent link

answered 25 Jan '13, 12:13

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

edited 25 Jan '13, 12:21

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
1

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

(25 Jan '13, 12:31) tunguyen825

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×53

question asked: 25 Jan '13, 11:00

question was seen: 6,001 times

last updated: 27 Jan '13, 12:28