Re: Problem with simple SQL query part 2

Have you tried GROUP BY instead of ORDER BY?

Terry Maguire wrote:

> 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 Thu Oct 01 1998 - 00:00:00 CDT