Difference between revisions of "Getting the latest record"

From SQLZOO
Jump to: navigation, search
(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:gisq</div>
+
<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 16: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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense