Difference between revisions of "Getting the latest record"
From SQLZOO
(Created page with "How can I get the Lastest record from SQL <div class='ht'> <div class=params>schema:gisq</div> <div> I have a table like this: <pre> balance keepplace startdate _______...") |
|||
| Line 1: | Line 1: | ||
How can I get the Lastest record from SQL | How can I get the Lastest record from SQL | ||
<div class='ht'> | <div class='ht'> | ||
| − | <div class=params>schema: | + | <div class=params>schema:scott</div> |
<div> | <div> | ||
I have a table like this: | I have a table like this: | ||
| Line 40: | Line 40: | ||
GROUP BY balance; | GROUP BY balance; | ||
SELECT balance, keepplace, startdate | SELECT balance, keepplace, startdate | ||
| − | FROM placement, X | + | FROM gisq.placement, X |
WHERE startdate = X.S</source> | WHERE startdate = X.S</source> | ||
<source lang='sql' class='def'>CREATE TABLE placement(balance CHAR(3) NOT NULL | <source lang='sql' class='def'>CREATE TABLE placement(balance CHAR(3) NOT NULL | ||
Revision as of 15:21, 17 July 2012
How can I get the Lastest record from SQL
schema:scott
I have a table like this:
balance keepplace startdate ___________________________________ A01 floor 1 2002-10-10 A01 BBB 2001-08-08 A02 222 2002-07-08 A02 111 2000-01-01 A03 ....
I want to get " where is the keep place for all balance by now? Answer Use a sub-select to get the last date - and make that match the original...
CREATE TABLE placement(balance CHAR(3) NOT NULL ,keepplace VARCHAR(10) ,startdate DATE NOT NULL ,PRIMARY KEY(balance,startdate) ); INSERT INTO placement VALUES('A01','floor 1','2002-10-10'); INSERT INTO placement VALUES('A01','BBB','2002-08-08'); INSERT INTO placement VALUES('A02','222','2002-07-08'); INSERT INTO placement VALUES('A02','111','2002-01-01'); DROP TABLE X; CREATE TABLE X ( B CHAR(3) NOT NULL PRIMARY KEY ,S DATE ); INSERT INTO X SELECT balance AS B, MAX(startdate) AS S FROM gisq.placement GROUP BY balance; SELECT balance, keepplace, startdate FROM gisq.placement, X WHERE startdate = X.S
CREATE TABLE placement(balance CHAR(3) NOT NULL ,keepplace VARCHAR(10) ,startdate DATE NOT NULL ,PRIMARY KEY(balance,startdate) ); INSERT INTO placement VALUES('A01','floor 1','2002-10-10'); INSERT INTO placement VALUES('A01','BBB','2002-08-08'); INSERT INTO placement VALUES('A02','222','2002-07-08'); INSERT INTO placement VALUES('A02','111','2002-01-01'); DROP TABLE X; CREATE TABLE X ( B CHAR(3) NOT NULL PRIMARY KEY ,S DATE ); INSERT INTO X SELECT balance AS B, MAX(startdate) AS S FROM placement GROUP BY balance; SELECT balance, keepplace, startdate FROM placement, X WHERE startdate = X.S
