Re: Problem with a simple SQL query!

Your subquery won't work as rownum is evaluated before the order by clause so it might not retrieve the data you requested and the order by clause itself is not relevant in a subquery as Mark Styles pointed out.

If the order is important then use the order by clause when you retrieve from the table.

 Al

Terry Maguire <tmaguire_at_nospam.tinet.ie> wrote in message 6ut3ee$cd315_at_kirk.tinet.ie...
>
>Mark,
>
>Thank you for your wonderful insight but that is exactly what I'm trying to
>do!!!
>
>The ORDER BY is for the SELECT statement and not the INSERT. What is the
>correct SQL statement to do what I want, please?
>
>Regards
>
>Terry Maguire
>
>
>Mark Styles wrote in message <36120acd.9246615_at_news.intra.bt.com>...
>>Wed, 30 Sep 1998 10:28:15 +0100, "The Phantom"
>><tmaguire_at_nospam.tinet.ie> rambled:
>>>Here is the statement:
>>>
>>>insert into t_sedol (sedol)
>>>select sedol from t_master_download
>>>where rownum<=246
>>>and country='United Kingdom'
>>>and downloaddate='01-DEC-92'
>>>order by market_cap desc;
>>>
>>>So basically I want to insert the 'sedol' value(s) into the table
 't_sedol'.
>>>The 'sedol' value(s) is a SELECT query that chooses its data from the
 table
>>>'t_master_download'. When I remove the ORDER part of the statement it
 runs
>>>fine but the ORDER is essential. Can anyone help on this one?
>>
>>An ORDER BY doesn't make any sense on an insert statement. Data in an
>>RDBMS is not stored in an ordered way. If you select from a table
>>without using an ORDER BY, there is no guarantee of the order you get
>>the data returned.
>>
>>Insert the data without the order by, then select from the table with
>>an order by.
>>
>>Mark Styles
>>Spam my account, lose your account. Clear enough?
>>Religious tolerance is an oxymoron.
>
>
Received on Wed Sep 30 1998 - 00:00:00 CEST