Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to avoid duplicates here...?

Re: How to avoid duplicates here...?

From: Kalmact <kalmact_at_hotmail.com>
Date: 8 Apr 2004 01:12:08 -0700
Message-ID: <8007e332.0404080012.35141b03@posting.google.com>


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1081392634.888413_at_yasure>...
> Spare Brain wrote:
>
> > 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
>
> Please don't post to multiple groups: One is enough.
>
> SELECT emp_name
> FROM (
> SELECT DISTINCT emp_name, rownum
> FROM employee
> WHERE dept = 'hardware')
> WHERE rownum BETWEEN 10 AND 15;

This would not prevent the duplicates from showing up since rownum will make it unique. The correct solution would be select emp_name from (
select rownum rnum,emp_name
from
(
select distinct(emp_name)
from employee
where dept = 'hardware' ) )
where rnum between 10 and 15 Received on Thu Apr 08 2004 - 03:12:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US