Take the sorting and limiting out of the subquery.
Like this:
SELECT LETZTER.ANLDETAIL_WKN, LETZTER.DATUM, VORLETZTER.DATUM
FROM TICKER
INNER JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
INNER JOIN (
SELECT ANLDETAIL_WKN, DATUM
FROM ANLKURSE
)AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
AND VORLETZTER.ID<> LETZTER.ID
ORDER BY LETZTER.DATUM DESC, VORLETZTER.DATUM DESC
LIMIT 1
This assumes that table ANLKURSE has a unique key column.
I have not tested this but just wrote it here in case it might prove
helpful to you.
And in the future formulate your questions about sql in such a fashion
that it is
simple for others to try out.
Eg. Create the following tables
<3 create table statements>
Execute the following query
<select statement>
Expected result
<something>
Current result
<something else>
- Rami
On 29.5.2012 11:57, Dani wrote:
Hey there,
I have different queries where I am collecting/grouping data around
their ID and their 2 topmost dates(last 2 dates of entry for each):
--> example:
SELECT
LETZTER.ANLDETAIL_WKN,
LETZTER.DATUM,
VORLETZTER.DATUM
FROM
TICKER
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC LIMIT 1
)AS LETZTER
ON LETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN
INNER JOIN
(
SELECT
ANLDETAIL_WKN,
DATUM
FROM
ANLKURSE
ORDER BY
ANLKURSE.DATUM DESC LIMIT 1,1
)AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
