How to query previous rows of a table in a select statement if the table is resorted by a non-primary key

I have a table named Clients with three columns - RowID, ClientNumber, and ClientNote where RowID is the primary key. Each ClientNumber has multiple ClientNotes so that the data looks like this:

RowId    ClientNumber    ClientNote
1        678             Note 1
2        678             Note 2
3        123             Note 3
4        123             Note 4
5        123             Note 1
6        F45             Note 3
7        F45             Note 6

I am trying to return a table that sorts the data by ClientNumber, and also adds a column that could be used to print different color backgrounds if needed:

ClientNumber    ClientNote   Color
123             Note 3       Blue
123             Note 4       Blue
123             Note 1       White
678             Note 1       White
678             Note 2       White
F45             Note 3       Blue
F45             Note 6       Blue          

I am trying to use a CASE statement, where my base case (row 1) is set to Blue, and I would like to then access the previous row to query data to make a decision on color.

Here is my code:

SELECT ClientNumber , ClientNote,
CASE 
WHEN (SELECT @rownum:=@rownum+1 rownum) = 1
THEN 'Blue'

WHEN @rownum != 1 AND (SELECT ClientNumber FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = ClientNumber AND (SELECT 'Color' FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = 'White'
THEN 'White'

WHEN @rownum != 1 AND (SELECT ClientNumber FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = ClientNumber AND (SELECT 'Color' FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = 'Blue'
THEN 'Blue'

WHEN @rownum != 1 AND (SELECT ClientNumber FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) != ClientNumber AND (SELECT 'Color' FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = 'Blue'
THEN 'White'

WHEN @rownum != 1 AND (SELECT ClientNumber FROM Clients WHERE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) != ClientNumber AND (SELECT 'Color' FROM Clients WHE开发者_如何学编程RE @rownum - 1 < @rownum ORDER BY ClientNumber ASC LIMIT 1) = 'White'
THEN 'Blue'

END
AS Color
FROM (SELECT @rownum:=0) r, Clients 
ORDER BY ClientNumber ASC

Essentially, I am just looking back to see if the previous ClientNumber matches or not, and inserting a color based on the previous color. I am having a problem querying the ClientNumber on the previous row in my new table. I am using MySQL-server, and I don't know a lot about SQL.

The other problem I am having is querying the color of the previous row. Since I am using a CASE statement, I think I might have to access the column information instead of using the name of the column, but I am not sure if this is necessary.

The problem and solution are pretty basic, but the syntax is killing me!! I've tried just about everything I can think of (the above code is the latest version) except joins, which I don't even really understand.

Any explanation of how to access "previous" (if they can really be called that in a database) rows and CASE statement results would really be appreciated.

MS-SQL is not FoxPro where you can do SCAN ... END SCAN and sequentially iterate through all the records and/or even move the pointer to any row you want. So I don't think the "refer to the previous row" aproach will work on the SQL server. You can use ADO.NET or anything else that fetches the data in some form of a DataTable or simply a collection of rows in a WinForms application. This will allow you more flexibility in referring to a previous row (or any row) while iterating the rows sequentially. Honestly I still don't understand the algorithm that decides the colours (maybe you should present the algorithm and not the code). However there are ways to go through the rows of a table in SQL using cursors but I don't think that's enough for you; anyway if it helps you may want to take a look at this other thread.