Bug in SELECT from SELECT ?

I have a table intwb_items

CREATE TABLE intwb_items
(
    wb_id integer not null,
    prod_id integer not null,
    qty numeric(12,5) not null,
    primary key(wb_id, prod_id)
)

How can I obtain sum of 10 greatest "qty"?
I tried to execute query:

SELECT sum(qty) FROM
(
  SELECT top 10 qty
  FROM intwb_items ORDER BY qty DESC
)as tmp

But it raise error:

"Syntax error or access violation: near 'ORDER' in ... FROM intwb_items
[ORDER]"

The same query but without "ORDER BY" works perfectly, but "TOP N" without
"ORDER" is very foolish ;)

I'v tried it in ASA 6.0.4.3779 and evaluation ASA 7.0.3 whith identical
results.
---
Best regards, Alexander



0
Alexander
2/13/2002 2:56:55 PM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

1 Replies
776 Views

Similar Articles

[PageSpeed] 53

ORDER BY can only be used in the outer-most query expression; it cannot be
specified in a derived table or view.

Instead, you could insert the greatest 10 quantities into a temp table
(using TOP 10 and ORDER BY), and issue a second query to do the sum() over
that table.

SQL-OLAP language extensions that we are implementing for the 9.0 release of
SQL Anywhere will make it possible to do what you want with a single SQL
statement. Until then, I think the temp table solution is the only one
available to you.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering


"Alexander" <alexgold@xgl.dhs.org> wrote in message
news:XZLZP#JtBHA.304@forums.sybase.com...
> I have a table intwb_items
>
> CREATE TABLE intwb_items
> (
>     wb_id integer not null,
>     prod_id integer not null,
>     qty numeric(12,5) not null,
>     primary key(wb_id, prod_id)
> )
>
> How can I obtain sum of 10 greatest "qty"?
> I tried to execute query:
>
> SELECT sum(qty) FROM
> (
>   SELECT top 10 qty
>   FROM intwb_items ORDER BY qty DESC
> )as tmp
>
> But it raise error:
>
> "Syntax error or access violation: near 'ORDER' in ... FROM intwb_items
> [ORDER]"
>
> The same query but without "ORDER BY" works perfectly, but "TOP N" without
> "ORDER" is very foolish ;)
>
> I'v tried it in ASA 6.0.4.3779 and evaluation ASA 7.0.3 whith identical
> results.
> ---
> Best regards, Alexander
>
>
>


0
Glenn
2/13/2002 6:36:53 PM
Reply:

Similar Artilces:

SELECT from SELECT
Hi! I remember seeing an example where you can do a select from a nested select, but I cannot seem to make it work. So it would be something like this: select max( name ) from ( select name from customer union select name from vendor ) Was I dreaming I saw that? Thanks! D In article <4500243f@forums-2-dub>, daniel_AT_infrontsoftware_DOT_com@N0SPAM.com says... > Hi! > > I remember seeing an example where you can do a select from a nested > select, but I cannot seem to make it work. No, you weren't. Try (UNTESTED!) adding a...

Why is the select saying selected after an item is selected?
This was a whitelisting issue due to a software upgrade at hostgator..  Nothing wrong with my code..Scot King SEO Software Tool | Search Engine Optimization"Get your page rank high"  huh? It is smart to put your question in the body and not just in the title. Have no idea what your question is or if it even a question.Eric Pascarello.com | Twitter epascarello | LinkedIn use thisddl.options[ddl.selectedIndex].selected = false; //Clear the selection ddl.options[1].selected = trueMAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Sit...

superreview granted: [Bug 392746] Multiple selection can go wrong when selecting something before an existing selection : [Attachment 311145] patch
Robert O'Callahan (:roc) (reduced activity March 18 to April 9) <roc@ocallahan.org> has granted superreview: Bug 392746: Multiple selection can go wrong when selecting something before an existing selection https://bugzilla.mozilla.org/show_bug.cgi?id=392746 Attachment 311145: patch https://bugzilla.mozilla.org/attachment.cgi?id=311145&action=edit ------- Additional Comments from Robert O'Callahan (:roc) (reduced activity March 18 to April 9) <roc@ocallahan.org> looks good ...

superreview cancelled: [Bug 181105] Selection behaves badly when SpellCheck Selection is on : [Attachment 141155] Make alternate selection types render properly
Neil Deakin <enndeakin@sympatico.ca> has cancelled Boris Zbarsky <bzbarsky@mit.edu>'s request for superreview: Bug 181105: Selection behaves badly when SpellCheck Selection is on http://bugzilla.mozilla.org/show_bug.cgi?id=181105 Attachment 141155: Make alternate selection types render properly http://bugzilla.mozilla.org/attachment.cgi?id=141155&action=edit ...

superreview requested: [Bug 181105] Selection behaves badly when SpellCheck Selection is on : [Attachment 141155] Make alternate selection types render properly
Boris Zbarsky <bzbarsky@mit.edu> has asked Simon Fraser <sfraser@aol.net> for superreview: Bug 181105: Selection behaves badly when SpellCheck Selection is on http://bugzilla.mozilla.org/show_bug.cgi?id=181105 Attachment 141155: Make alternate selection types render properly http://bugzilla.mozilla.org/attachment.cgi?id=141155&action=edit ------- Additional Comments from Boris Zbarsky <bzbarsky@mit.edu> >Index: content/base/src/nsSelection.cpp >@@ -2792,17 +2792,37 @@ nsSelection::LookUpSelection(nsIContent >+ mDomSelections[j]->GetIsCollapsed(&a...

superreview requested: [Bug 297172] Select box bug when dynamicly enabling and disabling multiple selects. : [Attachment 197270] Proposed patch
Boris Zbarsky <bzbarsky@mit.edu> has asked Peter Van der Beken <peterv@propagandism.org> for superreview: Bug 297172: Select box bug when dynamicly enabling and disabling multiple selects. https://bugzilla.mozilla.org/show_bug.cgi?id=297172 Attachment 197270: Proposed patch https://bugzilla.mozilla.org/attachment.cgi?id=197270&action=edit ...

superreview granted: [Bug 297172] [FIX]Select box bug when dynamicly enabling and disabling multiple selects. : [Attachment 197270] Proposed patch
Peter Van der Beken <peterv@propagandism.org> has granted Boris Zbarsky <bzbarsky@mit.edu>'s request for superreview: Bug 297172: [FIX]Select box bug when dynamicly enabling and disabling multiple selects. https://bugzilla.mozilla.org/show_bug.cgi?id=297172 Attachment 197270: Proposed patch https://bugzilla.mozilla.org/attachment.cgi?id=197270&action=edit ------- Additional Comments from Peter Van der Beken <peterv@propagandism.org> >Index: content/html/content/src/nsHTMLSelectElement.cpp >============================================================...

SELECT . FROM ( SELECT .. FROM )
Hi, In application we generate very effective SQL statements that are understandable by MS SQL Server and Oracle but give syntax errors on Sybase. I am talking about SELECT . FROM ( SELECT .. FROM a_table). For example: select 1 from (select abc_id from abc) or select 1 from (select abc_id from abc union all select abc_id from bcd) Is there any way to overcome this limitation? Any help will be greatly appreciated. Leonid Borisov Tefensoft On Wed, 15 May 2002 02:09:52 -0700, Leonid Borisov wrote: > Hi, > > > >...

Select in the select clause
How to use another select clause within a select clause itself. Like; select col1,col2,(select colx from table b) from table a . The above statement is throwing syntax error Thanks, Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92 http://www.cgvakindia.com "anand_group" <anand@cgvakindia.com> wrote: > How to use another select clause within a select clause itself. > Like; > select col1,col2,(select colx from table b) from table a . > The above statement is throwing syntax error Ensure that subquery returns only one row. And...

select in the select statement
Hi, I am trying to add another select statement in my original/outer/overall select statement. Something like: Select 1, 2, 3 = (Select # from Table1 Where....) From Table 2 Where.... Does anyone know the standard way to write this or is there any other alternative that can perform the same behavior. Much thanks!! Kenny That's database dependent. In oracle you would use: select A.* from ( select * from mytable) A -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase "Kenny Tran" <...

Select option selection
I'm created a gridview of websites, in one column, and use the gridview's 'selection' featue in the next column. When I click the 'selection' link I tried to get the website by using: Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(1).Text but it said it was not part of the webcontrol for selection. How do I get cell so that I can read it? thanks   Looks like your grabbing the select option and not the first cell  try  Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(0).TextwerD, MCSD VB.Net Hi, gmcghee: There are several ways...

insert select and select into
Hello, I am using temp tables to generate a report. I created most of the temp tables and then use insert select. I found for one, when I use insert select, I get a different query plan than if I use select into. The select into get a much better performance. The problem is I get dead locks on the statement. Is there anything I can do to change this behavior? Thanks, Josh Manfred ...

superreview granted: [Bug 185098] Font style ignored if <option selected="selected"> in a <select> that contains an optgroup : [Attachment 154999] Patch rev. 1
Boris Zbarsky <bzbarsky@mit.edu> has granted Boris Zbarsky <bzbarsky@mit.edu>'s request for superreview: Bug 185098: Font style ignored if <option selected="selected"> in a <select> that contains an optgroup http://bugzilla.mozilla.org/show_bug.cgi?id=185098 Attachment 154999: Patch rev. 1 http://bugzilla.mozilla.org/attachment.cgi?id=154999&action=edit ------- Additional Comments from Boris Zbarsky <bzbarsky@mit.edu> Yeah, this is reasonable. r+sr=bzbarsky. For future reference, using more context and the -p option makes the diff...

superreview granted: [Bug 112281] ctrl+shift+click whle making range selections in form <select> shouldn't wipe out other selected items : [Attachment 135533] Proposed patch
Robert O'Callahan <roc@ocallahan.org> has granted Boris Zbarsky (real world intrudes) <bz-vacation@mit.edu>'s request for superreview: Bug 112281: ctrl+shift+click whle making range selections in form <select> shouldn't wipe out other selected items http://bugzilla.mozilla.org/show_bug.cgi?id=112281 Attachment 135533: Proposed patch http://bugzilla.mozilla.org/attachment.cgi?id=135533&action=edit ------- Additional Comments from Robert O'Callahan <roc@ocallahan.org> looks quite straighforward :-) ...

Web resources about - Bug in SELECT from SELECT ? - sybase.sqlanywhere.general

Scottish Affairs Select Committee - Wikipedia, the free encyclopedia
The Scottish Affairs Select Committee is a select committee of the House of Commons in the Parliament of the United Kingdom . The remit of the ...

kotaku selects
Kotaku Selects

Facebook Testing Featured Videos in Videos Sections of Select Pages
Facebook confirmed to TechCrunch that it is testing a new design for the videos section of select pages – including ABC News and The New York ...

Hyundai set to bring CarPlay to select cars early next year after delay
... update to be released during the first quarter of 2016 that will enable the CarPlay feature on supported models with a connected iPhone. Select ...

How to Select Sharable Curated Content
Creating and delivering fresh content on a frequent and consistent basis can be a challenge in today’s demanding digital world. Curated content ...

Google Play offering select buy one, gift one movies for the holidays
... a few days ago, Google is giving you the chance to purchase a movie and gift that same movie to a friend for no extra charge. The selection ...

Oddisee, OSHUN & Tunji Ige played BrooklynVegan-Red Bull Sound Select at Baby's All Right
... / OSHUN / Tunji Ige @ Baby's All Right - 1/19/15 Oddisee headlined Tuesday night's BrooklynVegan-curated installment of Red Bull Sound Select ...

Dezeen selects 10 of David Bowie's most iconic moments
David Bowie was one of the most influential fashion and design icons of the last 40 years. Dezeen picks his best looks, costumes and album covers ...

Cortana Exits Beta, Available for Select Android and Cyanogen OS Powered Devices
... , the digital assistant made to be the perfect companion for Windows users, has officially exited beta, made available to a number of select ...

EA’s Origin Access offers PC gamers unlimited access to select games
For $5 per month you get early access to EA titles on Origin, plus a selection of older games from The Vault at no additional cost.

Resources last updated: 1/23/2016 7:42:07 PM