query [message #372096] |
Wed, 10 January 2001 01:58 |
debasish
Messages: 14 Registered: January 2001
|
Junior Member |
|
|
Sir,
I have a table of parts below:
PC PNAME COLOUR REORDER_LEVEL QOH CITY WEIGHT
-- --------------- ---------- ------------- --------- --------------- ---------
P1 NUT RED 100 150 LONDON 17
P2 BOLT GREEN 100 150 PARIS 19
P3 SCREW BLUE 200 300 ROME 21
P4 SPANNER RED 300 450 LONDON 19
P5 CAM BLUE 350 400 PARIS 15
P6 COG RED 200 250 LONDON 14
i want to retrieve the part information which is having second highest weight in the parts table.
|
|
|
Re: query [message #372109 is a reply to message #372096] |
Wed, 10 January 2001 08:41 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
Same as previous Q answer
select partno,weight from parts a
where &nthmaxweight= (select count(distinct(weight)) from parts b where a.weight<=b.weight )
Suresh Vemulapalli
|
|
|
Re: query [message #372130 is a reply to message #372109] |
Fri, 12 January 2001 03:00 |
debasish
Messages: 14 Registered: January 2001
|
Junior Member |
|
|
Sorry sir, the query couldn't be executed, which i have asked u before.
Q 1.I have a table of parts below:
PC PNAME COLOUR REORDER_LEVEL QOH CITY WEIGHT
-- --------------- ---------- ------------- --------- --------------- ---------
P1 NUT RED 100 150 LONDON 17
P2 BOLT GREEN 100 150 PARIS 19
P3 SCREW BLUE 200 300 ROME 21
P4 SPANNER RED 300 450 LONDON 19
P5 CAM BLUE 350 400 PARIS 15
P6 COG RED 200 250 LONDON 14
i want to retrieve the part information which is having second highest weight in the parts table.
--------------------------------------------------
Q 2.I hv a table of orders:
ORDER_NO SC PC QTY_SUPPLIED ORDER_DAT SUPPLY_DA
--------- -- -- ------------ --------- ---------
1 S1 P1 300 12-JAN-97
2 S1 P2 200 12-JAN-97
3 S1 P3 400 15-JAN-97
4 S1 P4 200 18-FEB-97
5 S1 P5 100 22-MAR-97
6 S1 P6 100 25-MAR-97
7 S2 P1 300 28-MAY-97
8 S2 P2 400 23-JUN-97
9 S3 P2 200 23-OCT-97
10 S4 P2 200 14-NOV-97
11 S4 P4 300 18-DEC-97
12 S4 P5 400 18-JAN-97
Query is : To retrieve supplier code who supplies all the parts from p1 to p6.
|
|
|