Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #185116] Mon, 31 July 2006 01:33 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi everybody



CREATE TABLE VEH (VNO number primary key,vname varchar2(20),chasi varchar2(10));

CREATE TABLE BOOK (VNO NUMBER REFERENCES VEH(VNO),START_KMS NUMBER,END_KMS NUMBER);

INSERT INTO VEH VALUES(1,'maruti800',1234);
INSERT INTO VEH VALUES(2,'marutiVAN',2345);
INSERT INTO VEH VALUES(3,'marutiWAGONR',3456);
INSERT INTO VEH VALUES(4,'hyundaiACCENT',4567);

INSERT INTO BOOK VALUES(1,0,200);
INSERT INTO BOOK VALUES(1,200,500);
INSERT INTO BOOK VALUES(2,0,500);
INSERT INTO BOOK VALUES(2,500,1100);
INSERT INTO BOOK VALUES(3,0,400);
INSERT INTO BOOK VALUES(3,400,600);
INSERT INTO BOOK VALUES(1,500,2000);
INSERT INTO BOOK VALUES(1,2000,2200);
INSERT INTO BOOK VALUES(2,1100,3000);

Question:1 Give the details of the vehicle which is having maximum entries in book.


I tried like this

SELECT V.VNO,VNAME,CHASI FROM VEH V,
(SELECT VNO,COUNT(VNO) CNT FROM BOOK GROUP BY VNO HAVING COUNT(VNO)=
(SELECT MAX(CNT1) FROM
(SELECT VNO,COUNT(VNO) CNT1 FROM BOOK GROUP BY VNO))) B
WHERE V.VNO=B.VNO


and it is giving correct result I just wanted to know is there any better method is there for this?
Re: Query [message #185132 is a reply to message #185116] Mon, 31 July 2006 02:17 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a different way to do it:
SELECT b.vno, v.vname, v.chasi
  FROM veh v, (SELECT   vno, RANK () OVER (ORDER BY COUNT (*) DESC) rnk
                   FROM book
               GROUP BY vno) b
 WHERE v.vno = b.vno 
   AND rnk = 1;
Re: Query [message #185133 is a reply to message #185132] Mon, 31 July 2006 02:20 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member


Thanks alot
Re: Query [message #185136 is a reply to message #185116] Mon, 31 July 2006 02:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One Method would be ...


SELECT V.VNO,VNAME,CHASI , COUNT(*)
FROM VEH v ,Book b 
WHERE v.vno =b.vno 
GROUP BY V.VNO,VNAME,CHASI
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM Book b  GROUP BY b.VNO)


Rajuvan.
Re: Query [message #185149 is a reply to message #185116] Mon, 31 July 2006 04:11 Go to previous message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member

Thanks Rajuvan
Previous Topic: Delete duplicate rows
Next Topic: Can a procedure be called from a function
Goto Forum:
  


Current Time: Wed Dec 07 14:26:43 CST 2016

Total time taken to generate the page: 0.11586 seconds