Re: How to avoid duplicates here...?

"Spare Brain" <spare_brain_at_yahoo.com> wrote in message news:<c52c5t$l052_at_kcweb01.netnews.att.com>...
> Hi,
>
> I had a problem where I had to limit the rows returned - return only the
> rows between N and M. I accomplished it using a SQL that looks something
> like this:
>
>
> SELECT rownum, emp_name from EMPLOYEE
> WHERE dept = 'hardware'
> group by rownum, emp_name having rownum between 10 and 15
>
> Now, as it so happens, there could be multiple entries for any given
> employee. I am getting multiple rows for the same employee! Is there a way
> to introduce a "distinct" on just the emp_name? How else to achieve this? If
> I use "select distinct rownum, emp_name..." it does not prevent multiple
> employees from showing up!
>
> Please post your valuable suggestions to the newsgroup.
>
> Thanks!
> SB

SB, please do not cross-post; it is very bad form.

You appear to be misusing the Oracle rownum psuedo column. See the SQL manual for it's proper use and limitations.

In general to do what you want you have to select from a select that assigns the limit values sort of like:

select col_list from (
  select row_ctr, .... from table where .... order by row_ctr ) where row_ctr >= :N1
and row_ctr <= :N2

HTH -- Mark D Powell -- Received on Thu Apr 08 2004 - 15:24:51 CEST