OraFAQ Forum: SQL & PL/SQL » How to use distinct with 2nd column to display desired output

Home » SQL & PL/SQL » SQL & PL/SQL » How to use distinct with 2nd column to display desired output (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use distinct with 2nd column to display desired output [message #620052]

Mon, 28 July 2014 07:25

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

My requirement is i want to display loc column from dept table with distinct deptno from emp table and my desired output is like:-

LOC         DEPTNO
NEWYORK     10
DALLAS      20
CHICAGO     30

THIS CODE WORKS WELL BUT IT PRINTS DEPTNO FIRST:-

SELECT DISTINCT E.DEPTNO DEPTNO,D.LOC LOC
FROM SCOTT.DEPT D,SCOTT.EMP E
WHERE D.DEPTNO=E.DEPTNO

BUT WHEN I TRIED THIS CODE , IT GIVES ERROR:-

SELECT D.LOC LOC,DISTINCT E.DEPTNO DEPTNO
FROM SCOTT.DEPT D,SCOTT.EMP E
WHERE D.DEPTNO=E.DEPTNO

This query gives desired output but I do not want to use this:-

SELECT A.LOC,A.DEPTNO FROM ( SELECT DISTINCT E.DEPTNO DEPTNO,D.LOC LOC
FROM SCOTT.DEPT D,SCOTT.EMP E
WHERE D.DEPTNO=E.DEPTNO )A

I want to know How to use distinct with 2nd column to display desired output.
Please help
Thanks

Report message to a moderator

Re: How to use distinct with 2nd column to display desired output [message #620054 is a reply to message #620052]

Mon, 28 July 2014 07:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

but I do not want to use this:

Why?

Quote:

I want to know How to use distinct with 2nd column to display desired output.

You can't.

One correct query is:

select dname, loc
from dept
where deptno in (select deptno from emp)
/

Report message to a moderator

Re: How to use distinct with 2nd column to display desired output [message #620058 is a reply to message #620052]

Mon, 28 July 2014 07:51

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

From the documentation :
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#sthref6733
"Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list. (The bold is of my hand)

So, I suppose you can't. The distinct works on everything in the resultset (select list). The distinct does not work on a specific column.

Report message to a moderator

Re: How to use distinct with 2nd column to display desired output [message #620061 is a reply to message #620058]

Mon, 28 July 2014 08:20

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

martijn wrote on Mon, 28 July 2014 18:21

The distinct does not work on a specific column.

Yes it does, if you project only one column Razz

Perhaps you mean to say,

distinct col1, col2

Above applies distinct to both columns together to make the set of values unique and removes any other rows with similar set of values. Similarly, concatenating the columns would also apply distinct on both columns.

Report message to a moderator

Previous Topic:	Update a single character within string column
Next Topic:	Generating the Range for the given dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:25:32 CDT 2024