Home » SQL & PL/SQL » SQL & PL/SQL » Selecting uniquw Rows from Table contains duplicate rows. (Oracle 10G)
Selecting uniquw Rows from Table contains duplicate rows. [message #357873] Fri, 07 November 2008 00:19 Go to next message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
select * FROM users
WHERE rowid not in
(SELECT MIN(rowid) from
users where user_id = 472402)
and user_id = 472402


Please suggest by using his query still i am getting duplicate rows.

Thanx
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #357875 is a reply to message #357873] Fri, 07 November 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you read your query you see you select all rows but the one that has the min rowid.
So if you have 3 duplicates: 3-1 = 2 remaining rows.

Regards
Michel
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #357932 is a reply to message #357873] Fri, 07 November 2008 05:25 Go to previous messageGo to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Hi Sumedh7,

Try using the partition concept?

SELECT user_name
row_number() over(partition by user_name order by
user_name) user_number
From users
WHERE user_number = 1;

This example would be helpful to you.



Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358013 is a reply to message #357932] Fri, 07 November 2008 14:17 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@shobanasree,

A few pointers:
1/ About Column Aliases: A Basic Rule is that Column aliases can be referenced in the ORDER BY clause but in no other clauses in a statement.
SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> SELECT empno, sal SALARY from emp
  2  where SALARY > 1000;
where SALARY > 1000
      *
ERROR at line 2:
ORA-00904: "SALARY": invalid identifier


2/ Regarding the Order of Execution of a SQL when using analytic functions:
From Oracle SQL Reference

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.



So based on the above two points, I guess your example is wrong.

Regards,
Jo
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358045 is a reply to message #357873] Fri, 07 November 2008 23:34 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
sumedh7 wrote on Fri, 07 November 2008 00:19

Please suggest by using his query still i am getting duplicate rows.



Yes you'll get Duplicate rows, if you don't want to c them, why don't you do GROUP BY or DISTINCT, or simply remove "NOT IN" and put "IN" in your query..
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358126 is a reply to message #358013] Sat, 08 November 2008 19:46 Go to previous messageGo to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
HI Joicejohn,

Even I had a same kind of scenario, to select unique rows from a table having duplicate records. I used the same kind of select statement (partition concept) and it worked fine for me. Why don't you try that example and then tell? If you get any error pls tell me what error did u get?
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358134 is a reply to message #358126] Sun, 09 November 2008 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@shobanasree,

Please read OraFAQ Forum Guide and don't use IM/SMS speak and format your post.

Also, please try your query and at least post something that is syntaxically correct.

Regards
Michel
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358196 is a reply to message #358126] Mon, 10 November 2008 01:16 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@shobanasree,

I didn't tell your concept of using Analytical Functions is wrong, now did I? I said your example(query) was wrong. And I proved my point using a sample query also. If you are certain the query posted by you works, why don't you post your SQL*Plus Session running the same query. Please have some patience to read the post before replying to it.

Regards,
Jo
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358197 is a reply to message #358196] Mon, 10 November 2008 01:24 Go to previous messageGo to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
SQL> SELECT *
2 FROM XX_USERS;

USER_NAME
----------------------------------------------------------------------------------------
ABC
ABC
EFG
EFG
EFG
HIJ

6 rows selected.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 BEGIN
3 FOR I IN (SELECT user_name,
4 row_number() over(partition by user_name order by
5 user_name)user_number
6 FROM xx_users) LOOP
7 IF I.user_number = 1 THEN
8 DBMS_OUTPUT.PUT_LINE (I.user_name);
9 END IF;
10 END LOOP;
11 END;
12 /
ABC
EFG
HIJ

PL/SQL procedure successfully completed.
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358208 is a reply to message #358197] Mon, 10 November 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@shobanasree

ONCE AGAIN READ AND FOLLOW FORUM GUIDE.

"How to format your post?" section:
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

"Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Selecting uniquw Rows from Table contains duplicate rows. [message #358209 is a reply to message #357932] Mon, 10 November 2008 02:05 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

@shobanasree,

shobanasree wrote on Fri, 07 November 2008 16:55
Hi Sumedh7,
SELECT user_name
       row_number() over(partition by user_name order by 
                         user_name) user_number
From users
WHERE user_number = 1;




I don't want to annoy you or something like that. But how does the PL/SQL code you posted is similar to the above code?
Anyways, you can achieve it through normal SQL:

SQL> SELECT * FROM TEST_TAB;

USER_NAME
----------
ABC
ABC
ABC
DEF
DEF
GHI

6 rows selected.

-- Method 1
SQL> SELECT a1.user_name FROM (
  2  SELECT user_name,
  3   ROW_NUMBER() OVER (PARTITION BY User_Name Order By user_name) user_number
  4   FROM test_tab) a1
  5  WHERE a1.user_number = 1;

USER_NAME
----------
ABC
DEF
GHI

3 rows selected.

-- Method 2 (A simple one)
SQL> SELECT DISTINCT user_name FROM test_tab ORDER BY user_name;

USER_NAME
----------
ABC
DEF
GHI

3 rows selected.



I hate to admit it but I think this discussion is going a bit off track from OP's requirement. I think Michel's first answer was explanatory enough for OP.

Regards,
Jo
Previous Topic: How to get only last 3 ocuurances of OUT_DATE (Merged)
Next Topic: exception handling
Goto Forum:
  


Current Time: Sun Dec 04 18:58:39 CST 2016

Total time taken to generate the page: 0.11851 seconds