Thanks Rami,
unique key for
ANLKURSE is over ANLDETAIL_WKN and DATUM (ID and date)
TICKER is ANLDETAIL_WKN
Your query does not show the result for every ID.
I came up with the following solution. It is fast but I don't like it
because in case I need data from another column of ANLKURSE the GROUP
BY clause will be a problem. I would have to add some more JOINS on
top:
SELECT
TICKER.ANLDETAIL_WKN,
VORLETZTER.VORLETZTER_DATUM,
VORLETZTER.LETZTER_DATUM
FROM
TICKER
INNER JOIN
(
SELECT
MAX(DATUM) AS VORLETZTER_DATUM,
LETZTER_DATUM,
ANLDETAIL_WKN
FROM
ANLKURSE
INNER JOIN
(
SELECT
MAX(DATUM) AS LETZTER_DATUM,
ANLDETAIL_WKN AS LETZTER_WKN
FROM
ANLKURSE
GROUP BY
ANLDETAIL_WKN
) AS LETZTER
ON LETZTER.LETZTER_WKN = ANLKURSE.ANLDETAIL_WKN
AND LETZTER.LETZTER_DATUM <> ANLKURSE.DATUM
GROUP BY
ANLDETAIL_WKN) AS VORLETZTER
ON VORLETZTER.ANLDETAIL_WKN = TICKER.ANLDETAIL_WKN;
script for a test db ->
SET CLUSTER '';
SET DEFAULT_TABLE_TYPE 0;
SET WRITE_DELAY 500;
SET DEFAULT_LOCK_TIMEOUT 2000;
SET CACHE_SIZE 16384;
;
CREATE USER IF NOT EXISTS XY PASSWORD '' ADMIN;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_547EF21C_5CAB_4AD2_8ED4_B85103E8CB7B START WITH
529 BELONGS_TO_TABLE;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_F1EDB90A_3E59_4C50_BE7F_67E7BFABB2CF START WITH
136 BELONGS_TO_TABLE;
CREATE SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_14A944B3_8EC2_488B_A30D_22510B08BEC7 START WITH
120 BELONGS_TO_TABLE;
CREATE CACHED TABLE PUBLIC.TICKER(
ANLDETAIL_WKN VARCHAR(25) NOT NULL SELECTIVITY 100
);
ALTER TABLE PUBLIC.TICKER ADD CONSTRAINT PUBLIC.CONSTRAINT_17 PRIMARY
KEY(ANLDETAIL_WKN);
-- 2 +/- SELECT COUNT(*) FROM PUBLIC.TICKER;
INSERT INTO PUBLIC.TICKER(ANLDETAIL_WKN) VALUES
('855018'),
('620200');
CREATE CACHED TABLE PUBLIC.ANLKURSE(
ANLDETAIL_WKN VARCHAR(25) NOT NULL SELECTIVITY 1,
KURS DECIMAL(10, 4) DEFAULT 0 SELECTIVITY 72,
DATUM DATE NOT NULL SELECTIVITY 37
);
ALTER TABLE PUBLIC.ANLKURSE ADD CONSTRAINT PUBLIC.CONSTRAINT_1 PRIMARY
KEY(ANLDETAIL_WKN, DATUM);
-- 20 +/- SELECT COUNT(*) FROM PUBLIC.ANLKURSE;
INSERT INTO PUBLIC.ANLKURSE(ANLDETAIL_WKN, KURS, DATUM) VALUES
('620200', 36.1600, DATE '2012-05-29'),
('855018', 46.4300, DATE '2012-05-29'),
('855018', 45.0000, DATE '2012-05-28'),
('855018', 43.8500, DATE '2012-05-25'),
('855018', 44.0000, DATE '2012-05-24'),
('855018', 42.7900, DATE '2012-05-23'),
('855018', 45.1500, DATE '2012-05-22'),
('855018', 43.0300, DATE '2012-05-21'),
('620200', 35.3600, DATE '2012-05-28'),
('620200', 34.8900, DATE '2012-05-25'),
('620200', 35.5900, DATE '2012-05-24'),
('620200', 35.5000, DATE '2012-05-23'),
('620200', 36.8000, DATE '2012-05-22'),
('620200', 36.0000, DATE '2012-05-21'),
('620200', 35.3300, DATE '2012-05-30'),
('855018', 44.6500, DATE '2012-05-30'),
('620200', 34.6300, DATE '2012-05-31'),
('620200', 34.3400, DATE '2012-06-01'),
('855018', 43.0600, DATE '2012-06-01'),
('840400', 72.8500, DATE '2012-05-31');
CREATE INDEX PUBLIC.ANLKURSE_DATUM ON PUBLIC.ANLKURSE(DATUM);
CREATE INDEX PUBLIC.ANLKURSE_KURS ON PUBLIC.ANLKURSE(KURS);
CREATE INDEX PUBLIC.ANLKURSE_ANLDETAIL_WKN ON
PUBLIC.ANLKURSE(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.
