Difference between revisions of "Getting the latest record"

From SQLZOO
Jump to: navigation, search
 
(8 intermediate revisions by 3 users not shown)
Line 5: Line 5:
 
<div>
 
<div>
 
I have a table like this:
 
I have a table like this:
 +
 +
 
<pre>
 
<pre>
 
balance    keepplace    startdate
 
balance    keepplace    startdate
Line 32: Line 34:
 
INSERT INTO placement VALUES('A02','111','01 Jan 2002');
 
INSERT INTO placement VALUES('A02','111','01 Jan 2002');
 
SELECT balance, keepplace, startdate
 
SELECT balance, keepplace, startdate
FROM placement, (
+
FROM scott.placement, (
 
   SELECT balance AS B, MAX(startdate) AS S
 
   SELECT balance AS B, MAX(startdate) AS S
   FROM gisq.placement
+
   FROM scott.placement
 
   GROUP BY balance)
 
   GROUP BY balance)
 
WHERE startdate = S
 
WHERE startdate = S

Latest 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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense