How do you Dynamically number rows?

[chunkII123]

Alright, I have been searching around the net for an answer to a question - How does one dynamically number a queries rows, and then select a specific row from that? Heres what I found, and it's not working out for me

SELECT a.*, @num := @num + 1 b from test a, (SELECT @num := 0) d;

Now I tweaked it to work for my table -

SELECT
    membernumber,
    dateDue,
    @num := @num +1 AS Row_Number
FROM
    bills b,
    (SELECT @num := 0;) d
WHERE 
    b.membernumber = '15980'

This returns a table like this:

|membernumber | dateDue | Row_Number |
|-------------------------------------|
| 15980 |2009-12-03| 1 |
| 15980 |2010-01-05| 2 |
| 15980 |2010-02-03| 3 |
|-------------------------------------|

Here is what I am trying to do, as you see, I "dynamically" numbered the row, so that the query would have numbered rows, so that in the WHERE clause I could specify the previous row. The only problem is, I am at a loss on how I could code this to obtain the second to the last row, and in this case I need to get the dateDue value from row 2. However, next month, I would need to get the dateDue value from row 3. So essentially, I need to select the last row, minus 1, and retrieve the information from that row in column dateDue. This is an existing table in my database, that has all the customer records, and can't be changed with primary keys, and an auto increment, as every member is inputted, and it's not just the member whose account number is '15980'

Thanks ahead of time everyone!

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[towerbase]

Your SQL statement should include an ORDER BY clause otherwise you cannot guarantee that the records will appear in the desired order.

One way to solve your problem is to order your records in descending dateDue order and then select the second record by using LIMIT

SELECT
    membernumber,
    dateDue,
    @num := @num +1 AS Row_Number
FROM
    bills b,
    (SELECT @num := 0;) d
WHERE 
    b.membernumber = '15980'
ORDER BY dateDue DESC
LIMIT 1,1

The LIMIT has the effect of skipping one record and then selecting the next (one) record. This should have the desired result of delivering the second last record.

Andrew
Hampshire, UK

 

[chunkII123]

Andrew,

Thank you for the prompt reply, I do really appreciate it! I will give you statement a try first thing in the morning. If it works like I want it to, then I can essentially remove the row numbering, as they won't really matter, considering, we're using LIMIT to step back one row.

Thanks again,

Josh

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'