Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

adding DISTINCT?

Posted on 2005-05-04
8
Medium Priority
?
1,393 Views
Last Modified: 2011-10-03
hi

i do this

SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%JOHN%') WHERE (rnum >= 1) AND (rnum < 10)

and it works fine but it is returning doubles of some rows?

Can i do something like

SELECT distinct rnum, english, sd_table_primary_key FROM (SELECT distinct ROWNUM rnum, s.* FROM (SELECT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%JOHN%') WHERE (rnum >= 1) AND (rnum < 10)

(i tried this and it doesnt limit those duplicate rows)

thanks
AM
0
Comment
Question by:amoran
8 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13927485
If it doesn't limit it, then at least one of the fields in your select contains information that is not a duplicate.  Is your PK field in there?
0
 
LVL 6

Expert Comment

by:chedgey
ID: 13927656
Without knowledge of your tables and keys I would guess that it is the ROWNUM that is causing you problems. Try:

SELECT rnum, english, sd_table_primary_key
  FROM ( SELECT ROWNUM rnum, s.*
*****                             vvvvvvvvv
                FROM (SELECT DISTINCT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English
******                           vvvvvvvvv
                             FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE
                           WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID)
                               AND (SYSTEM_DATA.Implementation_ID = 16)
                               AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16)
                               AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63)
                          ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s
                WHERE upper(English) LIKE '%JOHN%')
      WHERE (rnum >= 1) AND (rnum < 10)

Also, why the plethora of parentheses on the individual predicates? The serve no function.

Regards

Chedgey
0
 

Author Comment

by:amoran
ID: 13927800
chedgy

that gives me

Error Type:
Database.ExecuteSqlReturningRecordset (0x80040E14)
ORA-01791: not a SELECTed expression [Rethrown]

thanks
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 6

Expert Comment

by:chedgey
ID: 13927852
Did you take out the two lines with ***** on them?

Take your original query and add the DISTINCT after the thrid (inner-most) SELECT statement on the first line and see what happens.

Regards

Chedgey
0
 

Author Comment

by:amoran
ID: 13927954
Yes I did that.

I didnt include the  vvvvvvvvv

I added the DISTINCT after the thrid (inner-most) SELECT statement on the first line and it gave me that error.

Thanks
AM
0
 
LVL 6

Expert Comment

by:chedgey
ID: 13928171
OK, spotted it. The meaning of the ORA-1791 is:

  ORA-01791 not a SELECTed expression
  Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
  Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

So I suggest that on your inner-most SELECT you make the selected items and the ORDER BY list the same. Only order by items and expressions that you have in the SELECT list.

I hope that works for you as I am off home now after a long day in the office.

Regards

Chedgey
0
 

Author Comment

by:amoran
ID: 13928235
i removed some of the data in the order by column and it wokrs better now

SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT distinct SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE,RETURN WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND RETURN.Company_No = 1124 AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%%') WHERE (rnum >= 1) AND (rnum < 10)

but now it is still returning duplicate values?
(one unique row is returned twice)
0
 
LVL 2

Accepted Solution

by:
EasyWriter earned 120 total points
ID: 13930864
One way I have solved the above problem, is to completely remove the nested select concept (when possible.)  I rebuild the statement using a GROUP BY concept, one field at a time.  It is faster than one might think.  It helps you see where duplication occurs so you can quickly fix it.  It would start like this:

Select rnum from SYSTEM_DATA GROUP BY rnum

Then you would simply begin adding the next field.  You have two choices with each field you add.  Sometimes you can get away with using an aggregate function like MAX or MIN, to make it fit in, and other times you may need to add it to the GROUP BY clause.  So you would use whichever of the following statements fits your data:

Select rnum, max(sd_table_primary_key) from SYSTEM_DATA GROUP BY rnum

or

Select rnum, sd_table_primary_key from SYSTEM_DATA GROUP BY rnum, sd_table_primary_key

And then you add the third field, etc.

There should be no reason why you shouldn't be able to use nested selects with group by if you do it carefully, one field at a time, running your query after each small addition, checking to make sure you haven't duplicated anything.  The moment you get a duplicate, you know exactly where it happend, and what element caused it, so you can immediately focus your attention on the resolution.

Hope it helps,

EasyWriter
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question