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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help - Query

RE: Help - Query

From: Shockey, David <DShockey_at_jwrinc.com>
Date: Fri, 16 Jun 2000 11:59:17 -0500
Message-Id: <10530.109575@fatcity.com> 





This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.



------_=_NextPart_001_01BFD7B5.DBECAFFC


Content-Type: text/plain;


        charset="iso-8859-1"



If I understand your question correctly, this should work.



select contractor_number, synthetic_code, max(issue_number) issue_number 
from contractor_rates


group by contractor_number, synthetic_code



-----Original Message-----


From: Mahip Narayan [mailto:Mahip_at_mahindrabt.com]
Sent: Friday, June 16, 2000 10:04 AM


To: Multiple recipients of list ORACLE-L
Subject: Help - Query




Hi Lists,


I have a table CONTRACTOR_RATES like this -
Primay key is - (CONTRACTOR_NUMBER,SYNTHETIC_CODE,ISSUE_NUMBER)


 Name                            Null?    Type
 ------------------------------- -------- ----
 CONTRACTOR_NUMBER               NOT NULL VARCHAR2(8)
 SYNTHETIC_CODE                  NOT NULL VARCHAR2(4)
 ISSUE_NUMBER                    NOT NULL NUMBER(6)
 BT_RATE                                  NUMBER(7,2)
 BASIC_RATE                               NUMBER(7,2)
 STORES_RATE                              NUMBER(7,2)





The objective is Given a particular contractor_number
List the maximum issue_number and relevant details(bt_rate,....)
synthetic_code wise. Can anybody pl. give the query.


Contractor_number     synthetic_code      Issue_number
11                        1                   5
12                        1                   10
11                        2                   3
11                        3                   1
11                        1                   6
11                        2                   8

Output if we input Contractor_number = 11 should be -
11                        1                   6  with other three fields
values
11                        2                   8  with other three fields
values
11                        3                   1  with other three fields


values



TIA,



Mahip Narayan


-- 
Author: Mahip Narayan
  INET: Mahip_at_mahindrabt.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01BFD7B5.DBECAFFC
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

<HTML>

<HEAD>

<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">


<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =

5.5.2650.12">


<TITLE>RE: Help - Query</TITLE>

</HEAD>

<BODY>




<P><FONT SIZE=3D2>If I understand your question correctly, this should =

work.</FONT>


</P>




<P><FONT SIZE=3D2>select contractor_number, synthetic_code, =

max(issue_number) issue_number </FONT>


<BR><FONT SIZE=3D2>from contractor_rates</FONT>

<BR><FONT SIZE=3D2>group by contractor_number, synthetic_code</FONT>

</P>




<P><FONT SIZE=3D2>-----Original Message-----</FONT>

<BR><FONT SIZE=3D2>From: Mahip Narayan [<A =

HREF=3D"mailto:Mahip_at_mahindrabt.com">mailto:Mahip_at_mahindrabt.com</A>]</F=
ONT>


<BR><FONT SIZE=3D2>Sent: Friday, June 16, 2000 10:04 AM</FONT>

<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>

<BR><FONT SIZE=3D2>Subject: Help - Query</FONT>

</P>

<BR>




<P><FONT SIZE=3D2>Hi Lists,</FONT>

<BR><FONT SIZE=3D2>I have a table CONTRACTOR_RATES like this -</FONT>

<BR><FONT SIZE=3D2>Primay key is - =

(CONTRACTOR_NUMBER,SYNTHETIC_CODE,ISSUE_NUMBER)</FONT>


</P>




<P><FONT =

SIZE=3D2>&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Null?&nbsp;&nbsp;&nbsp; =
Type</FONT>


<BR><FONT SIZE=3D2>&nbsp;------------------------------- -------- =

----</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;CONTRACTOR_NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL =
VARCHAR2(8)</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;SYNTHETIC_CODE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL =
VARCHAR2(4)</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;ISSUE_NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
NOT NULL NUMBER(6)</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;BT_RATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp; NUMBER(7,2)</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;BASIC_RATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
NUMBER(7,2)</FONT>


<BR><FONT =

SIZE=3D2>&nbsp;STORES_RATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
NUMBER(7,2)</FONT>


</P>




<P><FONT SIZE=3D2>The objective is Given a particular =

contractor_number</FONT>


<BR><FONT SIZE=3D2>List the maximum issue_number and relevant =

details(bt_rate,....)</FONT>


<BR><FONT SIZE=3D2>synthetic_code wise. Can anybody pl. give the =

query.</FONT>


</P>




<P><FONT SIZE=3D2>Contractor_number&nbsp;&nbsp;&nbsp;&nbsp; =

synthetic_code&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Issue_number</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</FONT>


<BR><FONT =

SIZE=3D2>12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8</FONT>


</P>




<P><FONT SIZE=3D2>Output if we input Contractor_number =3D 11 should be =

-</FONT>


<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp; with other three =
fields</FONT>


<BR><FONT SIZE=3D2>values</FONT>

<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp; with other three =
fields</FONT>


<BR><FONT SIZE=3D2>values</FONT>

<BR><FONT =

SIZE=3D2>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; =
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; with other three =
fields</FONT>


<BR><FONT SIZE=3D2>values</FONT>

</P>




<P><FONT SIZE=3D2>TIA,</FONT>

<BR><FONT SIZE=3D2>Mahip Narayan</FONT>

</P>




<P><FONT SIZE=3D2>-- </FONT>

<BR><FONT SIZE=3D2>Author: Mahip Narayan</FONT>

<BR><FONT SIZE=3D2>&nbsp; INET: Mahip_at_mahindrabt.com</FONT>

</P>




<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =

538-5051&nbsp; FAX: (858) 538-5051</FONT>


<BR><FONT SIZE=3D2>San Diego, =

California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>


<BR><FONT =

SIZE=3D2>---------------------------------------------------------------=
-----</FONT>


<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =

E-Mail message</FONT>


<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =

'ListGuru') and in</FONT>


<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =

ORACLE-L</FONT>


<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =



Received on Fri Jun 16 2000 - 11:59:17 CDT












Original text of this message













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