Getting the latest record

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

DATE and TIME Operations

Date and time types

Format a date and time

Specify a date

Match a range of dates

Select the oldest person

Select a record with the latest date