How to avoid duplicates here...?

[Spare Brain]

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

[Mark D Powell]

"Spare Brain" <sp*********@yahoo.com> wrote in message news:<c5*********@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 --

[Dave]

"Spare Brain" <sp*********@yahoo.com> wrote in message news:<c5*********@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

select emp_name
from
(
select emp_name, rownum rnum
from
(
select distinct emp_name
from employee
where dept = 'hardware'
ORDER BY emp_name
)
where rownum < 16
)
where rnum > 9

Dave