Two queries into one

[Nick S.]

I have a table Invoices with columns Date1, Account and Amount, e.g.

Date1 Account Amount
3.3.2004 3 100
5.3.2004 2 20
18.5.2004 2 30
3.10.2004 1 10
18.11.2004 1 500
18.1.2005 3 15

I'd like to find for each account the row with the latest date for that
account.
The result should be:

18.11.2004 1 500
18.5.2004 2 30
18.1.2005 3 15

The query comprising of two steps and an intermediate table is easy:

first

SELECT Max(Date1) AS NewDate, Account AS NewAccount
INTO tbl1
FROM Invoices
GROUP BY Account;

then

SELECT Invoices.Date1, Invoices.Account, Invoices.Amount
FROM Invoices INNER JOIN tbl1
ON (Invoices.Account=tbl1.NewAccount) AND (Invoices.Date1=tbl1.NewDate);

How would that go in one step?

TIA,
Nick

 

[Marshall Barton]

I have a table Invoices with columns Date1, Account and Amount, e.g.

Date1 Account Amount
3.3.2004 3 100
5.3.2004 2 20
18.5.2004 2 30
3.10.2004 1 10
18.11.2004 1 500
18.1.2005 3 15

I'd like to find for each account the row with the latest date for that
account.
The result should be:

18.11.2004 1 500
18.5.2004 2 30
18.1.2005 3 15

The query comprising of two steps and an intermediate table is easy:

first

SELECT Max(Date1) AS NewDate, Account AS NewAccount
INTO tbl1
FROM Invoices
GROUP BY Account;

then

SELECT Invoices.Date1, Invoices.Account, Invoices.Amount
FROM Invoices INNER JOIN tbl1
ON (Invoices.Account=tbl1.NewAccount) AND (Invoices.Date1=tbl1.NewDate);

How would that go in one step?

SELECT T.Date1, T.Account, T.Amount
FROM Invoices As T
WHERE T.Date1=(SELECT Max(X.Date1)
FROM Invoices As X
WHERE T.Account=X.NewAccount)

 

[Nick S.]

SELECT T.Date1, T.Account, T.Amount
FROM Invoices As T
WHERE T.Date1=(SELECT Max(X.Date1)
FROM Invoices As X
WHERE T.Account=X.NewAccount)

Thanks a lot, Marsh. With the last line changed to
WHERE T.Account=X.Account)
it works like a dream.

Nick