Re: Query to fetch 100 distinct rows

kanchan_rohit_at_yahoo.com (Rohit Kanchan) wrote in message news:<9b16d634.0110060057.3640d8ca_at_posting.google.com>...
> The following query can be executed to list duplicate rows in a table:
>
> SELECT FROM duplicate_table
> WHERE rowid not in
> (SELECT MIN(rowid)
> FROM duplicate_table
> GROUP BY column1, column2, column3... ;
>
> Where column1, column2, column3 are the columns that make up the key
> that is supposed to uniquely identify the row.

John/Rohit,

   I'm sorry for not having been clear in describing the problem.

   I'll use the example cited by John to describe it: We have a Sample Table as follows:

Field1
a
b
c
a
a
b
c
a
a
d
e
f
g
h
i

   This table has 15 records in all of which 9 of them are unique (values a through i). Now a user is prompted for the number of unique records to fetch. If the user enters 3, we are to return records a,b & c.

   Please note that rowid cannot be used here since the actual query acts on a view which is a join of 4 tables and does satisy the condition of "must have one and only one key-preserved table".

Regards,
Ram. Received on Tue Oct 09 2001 - 11:22:08 CEST