473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to avoid duplicates here...?

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
2 5498
"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Alexander Anderson | last post by:
I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure of the table is little funny. The following is...
3
by: bellefy | last post by:
Hi All, I have a fairly large table with approx 30K rows that updates every night via a cron script that automatically downloads the 2 new csv's. The problem is the files are downloaded from...
6
by: Marlene | last post by:
Hi All I have the following scenario, where I have found all the duplicates in a table, based on an order number and a part number (item).I might have something like this: Order PODate Rec...
11
by: Jerry | last post by:
Say a table has four fields: ID (PK) Field1 Field2 Field3 Field 1 is indexed as Yes(No Duplicates) When a duplicate is entered in Field1, nothing happens until you try to save the record then...
3
by: AK | last post by:
Hi Our product uses MS-SQL Server 2000. One of our customer has 10 installations with each installation stroring data in its own database. Now the customer wants to consolidate these databases...
6
by: planetthoughtful | last post by:
Hi All, I have a C# ASP.NET page that submits back to itself to insert details from a form into a database table. When / if the user refreshes the page (and gets the standard warning that POST...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
2
by: jakeesgirl | last post by:
I'm new to c# and am creating a program in visual studio. I have several different forms in the program. Some of these forms need to access the same form. But I need to pass "this" to the form. ...
7
by: john.cole | last post by:
I have searched all the groups I can, and I still haven't been able to come up the solution I need. I have the following problem. In my form named sbfrmSpoolList, I am entering a job, spool and...
3
Thekid
by: Thekid | last post by:
I'm trying to figure out a way to find if there are duplicates in an array. My idea was to take the array as 'a' and make a second array as 'b' and remove the duplicates from 'b' using 'set' and then...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.