By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

How to avoid duplicates here...?

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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 --
Jul 19 '05 #2

P: n/a
"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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.