Subquery: how to retrieve the last non-zero value from a column?

Considering a table customerBalance with 3 columns: name, date and balance. Suppose a set of records like:

cus_name    cus_date    cus_balance
John        06/14/2011  1000
John        06/15/2011   500
John        06/16/2011     0
Mary        06/14/201开发者_如何学编程1  3000
Mary        06/15/2011  2800
Mary        06/16/2011     0

How to create a SQL query which returns, for the date 6/16/2011 instead 0, the last non-zero value based on date (in sample, $500 for John and $2800 for Mary)?

I'm trying to do it using a subquery which uses Max function to retrieve the last date with non-zero value, but I didn't succeed. This example is quite "nonsensical", but I really need to do an operation like this in my dataset. Thanks!

Note: If you can specify the DB and version this query can be improved.

Try this:

SELECT *
  FROM customers
 WHERE (cus_name, cus_date) 
        IN 
        (
            SELECT cus_name, MAX(cus_date)
              FROM customers
             WHERE cus_balance <> 0
             GROUP BY cus_name
        )

Update: Alternate version:

SELECT a.*
    FROM customers a,
            (
                SELECT cus_name, MAX(cus_date)
                    FROM customers
                 WHERE cus_balance <> 0
                 GROUP BY cus_name
            ) b
 WHERE a.cus_name = b.cus_name
   AND a.cus_date = b.cus_date

Here it goes:

CREATE Table #temp
(
    Cus_Name VARCHAR(200) NULL,
    Cus_Date Char(8) NULL,
    Cus_Balance INT NULL
)

INSERT INTO #temp VALUES ('John' , '20110614' ,1000 )
INSERT INTO #temp VALUES ('John' , '20110615' , 500 )
INSERT INTO #temp VALUES ('John' , '20110616' ,   0 )
INSERT INTO #temp VALUES ('Mary' , '20110614' ,3000 )
INSERT INTO #temp VALUES ('Mary' , '20110615' ,2800 )
INSERT INTO #temp VALUES ('Mary' , '20110616' ,   0 )

SELECT 
    T.Cus_Name ,
    MIN(t.Cus_Balance)
FROM #temp t 
WHERE t.Cus_Balance <>0
GROUP BY t.Cus_Name

DROP TABLE #temp