AW: 'select * from (select * from table order by field)' doesn't work

Alexei Novakov wrote:
> 
> Hello,
> 
> I'm running MaxDB 7.5.0.18 on Windows. In order to get
> first several ordered records I use usual query:
> 
> select * from
> (
>     select * from
>     some_table
>     order by some_column
> )
> where rowno < 2
> 
> I get the error here:
> 
> * -5016: Missing delimiter: ) SQLSTATE: 42000
> 
> Obviously it doesn't like 'order by' clause in
> subselect, as soon as I remove it like:
> 
> select * from
> (
>     select * from
>     some_table
> )
> where rowno < 2
> 
> It works fine, but it's not what I need. I have some
> feeling that such queries were working in older
> versions of SAPDB/MaxDB. 

That feeling is incorrect. SAPDB/MaxDB did not allow ORDER BY in
subqueries/from-selects.

> Is there some tricks to do to
> make it work? Or maybe there is other way to get
> limited sorted output in MaxDB?

Every client-'language' has some call to get the next resultrow (getnext
or how it may be named). Why not get rid of the from-select, just do 
select * from
    some_table
    order by some_column

and then loop over the results, counting them and stop if either error
100, row not found, or the wanted number of rows is found?


On the other hand:
There is a dirty trick, which will work up to a certain length of the
'order-columns'. Use group by instead of order by in the from-select.
I would never recommend doing this, but...

Elke
SAP Labs Berlin 

> 
> Thank you.
> 
> Alexei
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx