what is the sql statement to display first record in every group in job field in oracle9i?

select * from emp order by job.
in above sql statement, first recoord should display in every group in job field.
following  is a result
  7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
  7369 SMITH      CLERK             7902 17-DEC-80         800                   20
  7566 JONES      MANAGER        7839 02-APR-81        2975                  20
  7839 KING        PRESIDENT               17-NOV-81       5000                  10
  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300    30

what is the sql statements for above results ?


tamilsoftAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajexpertCommented:
Could you please post your expected results?
0
DavidSenior Oracle Database AdministratorCommented:
The "first recoord should display in every group" suggests your intent is to GROUP BY instead of ORDER BY as shown. Please clarify.
0
tamilsoftAuthor Commented:
I have typed orginal results in my first question.
select * from emp order by job ;
in this statement , in  job wise order I want
first record in clerk ,
first record in manager ,
first record in president,
first record in salesman,
first record in analyst.

how to give sql query.
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaju KumbalathDeputy General Manager - ITCommented:

SELECT * FROM (
SELECT EMP_ID, ENAME, JOB,ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY EMP_ID) AS SEQ FROM EMP) WHERE SEQ=1;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
What constitutes the "first" record (e.g. earliest date, lowest id, alphabetical name, etc.)?
0
jayeshshahCommented:
Try this one.

SELECT * from (
SELECT e.*, row_number() OVER(PARTITION BY job ORDER BY empno) AS rnum
FROM emp e) WHERE rnum = 1
;

In case if you woudl like to change your grouping then change the field name in the partition clause.
0
tamilsoftAuthor Commented:
ok. thank you mister "jayeshshah". I Have applied and got result.
some one place has low version oracle8.0.
how to apply in this version above requirement ?
0
jayeshshahCommented:
try to use RANK Analytical function of 8i. I think this should work.
0
jayeshshahCommented:
SELECT * from (
SELECT e.*, RANK() OVER(PARTITION BY job ORDER BY empno) AS rnum
FROM emp e) WHERE rnum = 1
0
awking00Commented:
The rank function should work in 8i. However, you still have not defined what determines the "first" record. It may well be by the lowest empno as has been proposed, but it may also be by the earliest hiredate. If so, just substitute that attribute in the order by clause of the rank function.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.