I haven't tried the ANSI OLAP syntax, but the Trafodion-specific variant works
fine:
>>select * from t1 order by a;
A B
----------- -----------
1 1
2 2
3 12
4 8
5 9
--- 5 row(s) selected.
>>select * from t1 where b in
+> (select b from (select b,rank(b) from t1 sequence by b) as t2(b,r)
+> where r <= 3);
A B
----------- -----------
2 2
1 1
4 8
--- 3 row(s) selected.
>>
From: Eric Owhadi [mailto:[email protected]]
Sent: Wednesday, December 21, 2016 12:30 PM
To: [email protected]
Subject: RE: LIMIT and ORDER BY in sub-queries
Hi Dave,
I thought about hat, but this cannot work without ORDER BY support. This will
only address the LIMIT.
Eric
From: Dave Birdsall [mailto:[email protected]]
Sent: Wednesday, December 21, 2016 2:26 PM
To:
[email protected]<mailto:[email protected]>
Subject: RE: LIMIT and ORDER BY in sub-queries
I do not know.
I wonder, though, if you could reformulate your subquery as an OLAP query and
add a predicate on RANK? That might have the effect you are looking for.
From: Eric Owhadi [mailto:[email protected]]
Sent: Wednesday, December 21, 2016 12:18 PM
To:
[email protected]<mailto:[email protected]>
Subject: LIMIT and ORDER BY in sub-queries
Hello Trafodioneers,
I see that we are not supporting LIMIT inside subqueries, and that ORDER BY
inside subqueries are ignored.
Is there a CQD to change this default behavior?
Thanks in advance for the help,
Eric
