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

Spare Brain wrote:

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;

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Daniel Morgan <damorgan@x.washington.edu> wrote


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

"Spare Brain" <spare_brain (AT) yahoo (DOT) com> wrote

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

"Spare Brain" <spare_brain (AT) yahoo (DOT) com> wrote

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