Problem with simple SQL query part 2

Hi

After the previous thread of messages, I'm not any closer to the solution of my problem. Thanks to all of you who wrote in with answers. This time I'm going to explain exactly what I'm trying to do. To refresh your memories here is the original query:
insert into t_sedol (sedol)

select sedol,market_cap from t_master_download

where rownum<=246

and country='United Kingdom'

and downloaddate='01-DEC-92'

order by market_cap desc;

1. Table T_MASTER_DOWNLOAD - Source Table
2. Table T_SEDOL - Destination Table

Thanks firstly to Mark Styles for pointing out that you cannot have an ORDER BY in a sub-query. Thanks also to Alan Patil pointing out my error with ROWNUM. But I ask you, how can I get around this problem:

The data in T_MASTER_DOWNLOAD contains financial data on companies. There are 4 specific criteria that I must work by to retrieve the data I need:

(a) The country e.g. United Kingdom
(b) The downloaddate e.g. 01-DEC-92
(c) The market capital of a company
(d) The amount of companies

The first 2 are easy to look after as these are in the WHERE clause. It's the next ones I'm having problems with. Market capital of a company is what we are using to determine if we analyse a company or not. So what I want to do in the SELECT statement is to have the highest market capital company at the top and then descend from that. Then what I require is to take the top 246 companies from this.

Now call me a traitor if you wish, but I had a look at how MS Access generates this type of SELECT query. Here we go

SELECT TOP 246 TERRY_T_MASTER_DOWNLOAD.SEDOL FROM TERRY_T_MASTER_DOWNLOAD
WHERE (((TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)>#11/1/92# And
(TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)<#1/1/93#) AND
((TERRY_T_MASTER_DOWNLOAD.COUNTRY)="United Kingdom"))
ORDER BY TERRY_T_MASTER_DOWNLOAD.MARKET_CAP DESC; It uses TOP 246, to grab the 246 highest market capital companies. Is there anything similar in Oracle PL/SQL??

The next step is to INSERT this data in the destination table T_SEDOL. I presumed it would be just a matter of putting the insert line, but that is obviously not the case. Again looking at MS Access they do this:

INSERT INTO TERRY_T_SEDOL ( SEDOL )
SELECT TOP 246 TERRY_T_MASTER_DOWNLOAD.SEDOL FROM TERRY_T_MASTER_DOWNLOAD
WHERE (((TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)>#11/1/92# And
(TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)<#1/1/93#) AND
((TERRY_T_MASTER_DOWNLOAD.COUNTRY)="United Kingdom"))
ORDER BY TERRY_T_MASTER_DOWNLOAD.MARKET_CAP DESC; Such a statement cannot be used in Oracle (forgetting all the MS Access crap). As Mark Styles pointed out, you can't use an ORDER BY in a subquery. Then how do you INSERT data in the manner I wish to. How do you do this type of INSERT??

Any help on these questions would be greatly appreciated!

Regards

Terry Maguire
IIU
IFSC House
Custom House Quay
Dublin 1
Ireland
e-Mail : tmaguire_at_nospam.tinet.ie
Remove nospam to e-Mail Received on Wed Sep 30 1998 - 00:00:00 CDT