Home » SQL & PL/SQL » SQL & PL/SQL » Select job, empno from scott.emp but distinct job ? (Oracle 10g Enterprise, openSuse 10.3)
Select job, empno from scott.emp but distinct job ? [message #316752] Mon, 28 April 2008 04:07 Go to next message
nvduc_82@yahoo.com
Messages: 3
Registered: April 2008
Junior Member
Hi all,
I run the query "select distinct job, empno from scott.emp order by job" (Using sample schema "Scott") and the result is
 
 JOB       EMPNO 
 --------- ----- 
 ANALYST    7788
 ANALYST    7902
 CLERK      7934
 CLERK      7900
 CLERK      7369
 CLERK      7876
 MANAGER    7698
 MANAGER    7566
 MANAGER    7782
 PRESIDENT  7839
 SALESMAN   7844
 SALESMAN   7654
 SALESMAN   7521
 SALESMAN   7499


I want the result as
 JOB       EMPNO 
 --------- ----- 
 ANALYST    7788
 CLERK      7934
 MANAGER    7698
 PRESIDENT  7839
 SALESMAN   7844

---> job is distinct (or the first ANALYST, the first Clerk, the first MANAGER, the first PRESIDENT, the first SALESMAN, ignore remained duplicate jobs)

Could you help me ? Thank you very much

[Updated on: Mon, 28 April 2008 04:11]

Report message to a moderator

Re: Select job, empno from scott.emp but distinct job ? [message #316754 is a reply to message #316752] Mon, 28 April 2008 04:14 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

What the output means and in which context you are using this report.

kanish
Re: Select job, empno from scott.emp but distinct job ? [message #316755 is a reply to message #316752] Mon, 28 April 2008 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Select job, empno from scott.emp but distinct job ? [message #316760 is a reply to message #316752] Mon, 28 April 2008 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As it seems that you don't care about EMPNO (you took "the first one" from the list; which ORDER BY did you use, if any?), a simple aggregate function might do the job:
select job, min(empno) empno
from emp
group by job
order by job;
Re: Select job, empno from scott.emp but distinct job ? [message #316761 is a reply to message #316755] Mon, 28 April 2008 04:35 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Excellent reference Michel.

This also meets the requirement:
SELECT job, empno from (
SELECT job, empno, ROW_NUMBER()
   OVER (PARTITION BY job ORDER BY job) AS emp_job
   FROM scott.emp
 order by job, empno)
where emp_job =1;

Re: Select job, empno from scott.emp but distinct job ? [message #316763 is a reply to message #316761] Mon, 28 April 2008 04:39 Go to previous messageGo to next message
nvduc_82@yahoo.com
Messages: 3
Registered: April 2008
Junior Member
Thank you very much. I am programming Java, using Hibernate. I selects all columns from one table and I want result set has distinct "employeeId".

For simplicity, I use sample schema SCOTT and express my idea: " job is distinct, I do not care about other columns "
Re: Select job, empno from scott.emp but distinct job ? [message #316768 is a reply to message #316763] Mon, 28 April 2008 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
job is distinct, I do not care about other columns

So why do you select them?

Regards
Michel
Re: Select job, empno from scott.emp but distinct job ? [message #316773 is a reply to message #316768] Mon, 28 April 2008 05:08 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
true..
all people will not 'read in between lines...
nice to quote
yours
dr.s.raghunathan
Re: Select job, empno from scott.emp but distinct job ? [message #316844 is a reply to message #316768] Mon, 28 April 2008 09:38 Go to previous message
nvduc_82@yahoo.com
Messages: 3
Registered: April 2008
Junior Member
Michel Cadot wrote on Mon, 28 April 2008 17:03
Quote:
job is distinct, I do not care about other columns

So why do you select them?

Regards
Michel


Hibernate is Java framework to handle database interaction, it maps Java objects to database table. When I select one Java object, Hibernate select all columns. Using Hibernate help me from verbose an ad-hoc raw SQL query and mapping database column's values to Java object's properties manually.
In my case, I want to filter by one column (job in my example) but Hibernate still select all columns. Of course, I can select only columns I like but as I said, I use Hibernate to avoid mapping manually and let Hibernate select all columns

[Updated on: Mon, 28 April 2008 09:39]

Report message to a moderator

Previous Topic: Staging table between 9i & 10gR2
Next Topic: sqlerrm,sqlcode
Goto Forum:
  


Current Time: Sun Dec 11 04:26:49 CST 2016

Total time taken to generate the page: 0.05116 seconds