Link to home
Start Free TrialLog in
Avatar of tamilsoft
tamilsoft

asked on

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 ?


Avatar of ajexpert
ajexpert
Flag of United States of America image

Could you please post your expected results?
Avatar of David VanZandt
The "first recoord should display in every group" suggests your intent is to GROUP BY instead of ORDER BY as shown. Please clarify.
Avatar of tamilsoft
tamilsoft

ASKER

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.
 
ASKER CERTIFIED SOLUTION
Avatar of Shaju Kumbalath
Shaju Kumbalath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What constitutes the "first" record (e.g. earliest date, lowest id, alphabetical name, etc.)?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
try to use RANK Analytical function of 8i. I think this should work.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial