Difference between revisions of "Getting the latest record"

From SQLZOO
Jump to: navigation, search
Line 21: Line 21:
 
</source>
 
</source>
 
<source lang=sql class='setup'></source>
 
<source lang=sql class='setup'></source>
−
<source lang='sql' class='def e-oracle'>CREATE TABLE placement(balance CHAR(3) NOT NULL
+
<source lang='sql' class='def e-oracle'>CREATE TABLE placement(balance CHAR(3)
 
   ,keepplace VARCHAR(10)
 
   ,keepplace VARCHAR(10)
−
   ,startdate DATE NOT NULL
+
   ,startdate DATE
 
   ,PRIMARY KEY(balance,startdate)
 
   ,PRIMARY KEY(balance,startdate)
 
   );
 
   );
−
INSERT INTO placement VALUES('A01','floor 1','2002-10-10');
+
INSERT INTO placement VALUES('A01','floor 1','10 Oct 2002');
−
INSERT INTO placement VALUES('A01','BBB','2002-08-08');
+
INSERT INTO placement VALUES('A01','BBB','08 Aug 2002');
−
INSERT INTO placement VALUES('A02','222','2002-07-08');
+
INSERT INTO placement VALUES('A02','222','08 Jun 2002');
−
INSERT INTO placement VALUES('A02','111','2002-01-01');
+
INSERT INTO placement VALUES('A02','111','01 Jan 2002');
−
DROP TABLE X;
+
SELECT balance, keepplace, startdate
−
CREATE TABLE X (
+
FROM placement, (
−
  B CHAR(3) NOT NULL PRIMARY KEY
+
−
  ,S DATE
+
−
  );
+
−
INSERT INTO X
+
 
   SELECT balance AS B, MAX(startdate) AS S
 
   SELECT balance AS B, MAX(startdate) AS S
 
   FROM gisq.placement
 
   FROM gisq.placement
−
   GROUP BY balance;
+
   GROUP BY balance)
 +
WHERE startdate = S
 +
</source>
 +
<source lang='sql' class='def e-sqlserver'>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
 
SELECT balance, keepplace, startdate
−
FROM gisq.placement, X
+
FROM placement, (
−
   WHERE startdate = X.S</source>
+
   SELECT balance AS B, MAX(startdate) AS S
−
<source lang='sql' class='def'>CREATE TABLE placement(balance CHAR(3) NOT NULL
+
  FROM placement
 +
  GROUP BY balance) X
 +
WHERE startdate = X.S</source>
 +
<source lang='sql' class='def e-mysql'>CREATE TABLE placement(balance CHAR(3) NOT NULL
 
   ,keepplace VARCHAR(10)
 
   ,keepplace VARCHAR(10)
 
   ,startdate DATE NOT NULL
 
   ,startdate DATE NOT NULL
Line 63: Line 74:
 
FROM placement, X
 
FROM placement, X
 
   WHERE startdate = X.S</source>
 
   WHERE startdate = X.S</source>
 +
<source lang='sql' class='def'>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</source>
  
 
<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 15:24, 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)
   ,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 gisq.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
DATE and TIME Operations
Date and time types
Format a date and time
Specify a date
Match a range of dates
Subtract dates
INTERVAL of time
Components of date
Date functions
Date arithmetics
Select the oldest person
Select a record with the latest date
YYYYMMDD date format
Getting the latest record
Retrieved from "http://sqlzoo.net/w/index.php?title=Getting_the_latest_record&oldid=1551"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense