Difference between revisions of "Getting the latest record"
From SQLZOO
| Line 2: | Line 2: | ||
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| + | <source lang=sql class='tidy'> DROP TABLE placement</source> | ||
| − | |||
<div> | <div> | ||
I have a table like this: | I have a table like this: | ||
Revision as of 06:40, 14 August 2012
How can I get the Lastest record from SQL
schema:scott
DROP TABLE placement
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) ,keepplace VARCHAR(10) ,startdate DATE ,PRIMARY KEY(balance,startdate) ); INSERT INTO placement VALUES('A01','floor 1','10 Oct 2002'); INSERT INTO placement VALUES('A01','BBB','08 Aug 2002'); INSERT INTO placement VALUES('A02','222','08 Jun 2002'); INSERT INTO placement VALUES('A02','111','01 Jan 2002'); SELECT balance, keepplace, startdate FROM scott.placement, ( SELECT balance AS B, MAX(startdate) AS S FROM scott.placement GROUP BY balance) WHERE startdate = S
USE scott; CREATE TABLE placement(balance CHAR(3) ,keepplace VARCHAR(10) ,startdate DATETIME ,PRIMARY KEY(balance,startdate) ); INSERT INTO placement VALUES('A01','floor 1','10 Oct 2002'); INSERT INTO placement VALUES('A01','BBB','08 Aug 2002'); INSERT INTO placement VALUES('A02','222','08 Jun 2002'); INSERT INTO placement VALUES('A02','111','01 Jan 2002'); SELECT balance, keepplace, startdate FROM placement, ( SELECT balance AS B, MAX(startdate) AS S FROM placement GROUP BY balance) 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
CREATE TABLE placement(balance CHAR(3) ,keepplace VARCHAR(10) ,startdate DATE ,PRIMARY KEY(balance,startdate) ); INSERT INTO placement VALUES('A01','floor 1','10 Oct 2002'); INSERT INTO placement VALUES('A01','BBB','08 Aug 2002'); INSERT INTO placement VALUES('A02','222','08 Jun 2002'); INSERT INTO placement VALUES('A02','111','01 Jan 2002'); SELECT balance, keepplace, startdate FROM placement, ( SELECT balance AS B, MAX(startdate) AS S FROM placement GROUP BY balance) X WHERE startdate = X.S
