Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> ***Urgent Question***

***Urgent Question***

From: Local Folders <asmika_at_satyam.net.in>
Date: Wed, 31 Jan 2001 15:32:58 +0530
Message-Id: <10758.127876@fatcity.com>


This is a multi-part message in MIME format.

------=_NextPart_000_00E7_01C08B9B.16BDC0A0 Content-Type: text/plain;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Consider the following case.
TABLE : DEPT

empid             number,
deptid             number,
deptname        varchar2(30)

primary key (empid, deptid)

now i want to fetch first seven records from a cursor whose deptid is = maximum
and empid is minimum. kindly let me know how to write this cursor = statement.for example.

SQL> select deptid, empid from dept;

 DEPTID EMPID

---------       ---------
        2     94204
        2     94205
        2     94206
        2     94207
        2     94208
        5     94209
        5     94210
        5     94211
        5     94212
        5     94213
        7     94214
        7     94215
        7     94216
        7     94217
        7     94218

15 rows selected.

i want output as following by single query.

 DEPTID EMPID

---------      ---------
        7     94214
        7     94215
        7     94216
        7     94217
        7     94218
        5     94209
        5     94210

7 rows selected

kindly reply.. thanks in advance.

regards
paresh mehta

------=_NextPart_000_00E7_01C08B9B.16BDC0A0 Content-Type: text/html;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3103.1700" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Consider the following case.<BR>TABLE : =

DEPT<BR>empid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;=20
number,<BR>deptid=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 number,<BR>deptname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 varchar2(30)<BR>primary key (empid, deptid)</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>now i want to fetch first seven records =
from a=20
cursor whose deptid is maximum<BR>and empid is minimum. kindly let me = know how=20
to write this cursor statement.for example.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>SQL&gt; select deptid, empid from=20
dept;</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;DEPTID&nbsp;&nbsp;=20
EMPID<BR>---------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 ---------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 2&nbsp;&nbsp;&nbsp;&nbsp; =
94204<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 2&nbsp;&nbsp;&nbsp;&nbsp; =
94205<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 2&nbsp;&nbsp;&nbsp;&nbsp; =
94206<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 2&nbsp;&nbsp;&nbsp;&nbsp; =
94207<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 2&nbsp;&nbsp;&nbsp;&nbsp; =
94208<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94209<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94210<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94211<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94212<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94213<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94214<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94215<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94216<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94217<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; 94218</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>15 rows selected.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>i want output as following by single=20
query.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;DEPTID&nbsp;&nbsp;=20
EMPID<BR>---------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 ---------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94214<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94215<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94216<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94217<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 7&nbsp;&nbsp;&nbsp;&nbsp; =
94218<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; =
94209<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20 5&nbsp;&nbsp;&nbsp;&nbsp; 94210</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>7 rows selected</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>kindly reply.. thanks in =
advance.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>regards<BR>paresh=20
Received on Wed Jan 31 2001 - 04:02:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US