Difference between revisions of "Getting the latest record"
From SQLZOO
| Line 91: | Line 91: | ||
WHERE startdate = X.S</source> | WHERE startdate = X.S</source> | ||
| + | <div class="ecomm e-sqlserver" style="display: none"></div> | ||
| + | <div class="ecomm e-oracle" style="display: none"></div> | ||
<div class="ecomm e-mysql" style="display: none">Sadly MySQL does not support nested SELECT statements nor does it allow views and so I don't see how this can be accomplished without copying the last date to another tables.</div> | <div class="ecomm e-mysql" style="display: none">Sadly MySQL does not support nested SELECT statements nor does it allow views and so I don't see how this can be accomplished without copying the last date to another tables.</div> | ||
</div> | </div> | ||
{{DATE and TIME ref}} | {{DATE and TIME ref}} | ||
Revision as of 11:00, 18 July 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
