1.7m Rows Consolidate By Most Recent Date

[tubbsy123]

Hi,

I have a table with 1,700,000 records in. A small cut of it is shown below (there are 5 more columns):

ACCOUNT_ID BILL_DATE BILL_TOTAL
7900000097 30-Sep-03 25.6
7900000097 24-Nov-03 37.21
7900000097 23-Feb-04 71.2
7900000160 08-Mar-04 150.91
7900000160 20-May-05 108.95

I need to select the most recent BILL_DATE for each ACCOUNT_ID. But I only require one row per ACCOUNT_ID.

Any help will be greatly appreciated.

Kind Regards

Tubbsy123

 

[PHV]

If you only want the 2 fields:
SELECT ACCOUNT_ID, Max(BILL_DATE) AS MostRecentBillDate
FROM YourTable
GROUP BY ACCOUNT_ID;

If more info per row is needed:
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT ACCOUNT_ID, Max(BILL_DATE) AS MostRecentBillDate
FROM YourTable GROUP BY ACCOUNT_ID
) AS B ON A.ACCOUNT_ID = B.ACCOUNT_ID AND A.BILL_DATE = B.MostRecentBillDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Try this:

SELECT Account_ID, Max(Bill_Date) AS MaxOfBill_Date
FROM YourTableNameHere
GROUP BY Account_ID;

http://www22.brinkster.com/accessory/

 

[lupins46]

The logic is simple. The problem is you will probably have to leave it running overnight to get a result.

Select * from mytable inner join (Select Account_id, max(Bill_date)as B from mytable group by ACcount_id) as q on mytable.account_id = q.account_id and mytable.bill_date = q.B

 

[KenReay]

Hi

Probably more than one way to do this, but for starters

two queries:

SELECT MyTable.ACCOUNT_ID, Last(MyTable.BILL_DATE) AS LastOfBILL_DATE
FROM MyTable
GROUP BY MyTable.ACCOUNT_ID;

then

SELECT MyTable.ACCOUNT_ID, MyTable.BILL_DATE, MyTable.BILL_AMOUNT
FROM MyTable INNER JOIN Query1 ON (MyTable.BILL_DATE = Query1.LastOfBILL_DATE) AND (MyTable.ACCOUNT_ID = Query1.ACCOUNT_ID);


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Ken, the LAST aggregate function is known to be unreliable.
Why not using MAX ?

 

[KenReay]

no (sensible) reason, I was interupted while formulating the answer and chose Last() instead of Max()

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK