Select all the rows up to the first 0 in a group

I am trying to build a list of clients and what their AR history was seance the last time it was 0. here is开发者_Go百科 a example

I want to turn this

ID       ClientID   Total   Balance
67202   122         63.01   63.01
66234   122         60.94   60.94
65738   122         278.47  0
62144   122         69.61   69.61
61662   122         13.65   13.65
61625   122         169.79  0
67618   49          47.37   47.37
62112   49          44.02   0

into this

ID     ClientID    Total   Balance
67202   122         63.01   63.01
66234   122         60.94   60.94
67618   49          47.37   47.37

Notice that the record 62144 and 61662 where not included because there was a 0 between them and the top two records.

Removing records like 62144 and 61662 is my hangup. If i just wanted to remove the 0's I could just do a Balance <> 0 but I only want the top records up to the first 0.

Here is the query I have so far to get me where I am now

select * from [Artrxs]
where [clientid] in (
    SELECT [ClientID]
    FROM [Artrxs]
    where [id] in (
        select max([id])  --Find the newest record for each client
        FROM [Artrxs] 
        group by clientid)
    and [balance] <> 0) --only show clients who's newest record is not 0
order by [clientid], [id] desc

Worst case, I am iterating over the list anyway in code for the report I can just start skipping till I hit a new client ID. But I would really like to be able to do this all in one SQL query.

You have to define what the "first" means for the first 0 in your query. In this case, as there's no datetime column I'm assuming the IDs are sequential by time, as well. That gives me this query:

WITH LastZeroByClient AS (
    SELECT ClientID, MAX(ID) AS ZeroID FROM Artrxs WHERE Balance = 0 GROUP BY ClientID
)
SELECT columns
FROM Artrxs t
INNER JOIN LastZeroByClient z ON h.ClientID=z.ClientID AND h.ID > z.ZeroID

Note that I originally had a LEFT JOIN, but then I noticed a snippet later in your question that said you want to exclude clients who's last ID is already zero.

Also, you could still do this with a derived table (sub query). I personally find derived tables more readable, but CTE's easier to write.