Subquery: how to get the last nonzero value from a column?

Question

Given a customerBalance table with three columns: name, date and balance. Assume a set of records, such as:

cus_name cus_date cus_balance John 06/14/2011 1000 John 06/15/2011 500 John 06/16/2011 0 Mary 06/14/2011 3000 Mary 06/15/2011 2800 Mary 06/16/2011 0 

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

I am trying to do this using a subquery that uses the Max function to get the latest date with a non-zero value, but I have failed. This example is pretty “pointless", but I really need to do such an operation in my dataset. Thanks!

Answer 1

Note. If you can specify the database and version, this query can be improved.

Try the following:

 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: alternative 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 

Answer 2

Here he is:

 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