Row_number() (1 Viewer)

[Kheribus]

I am developing a project using xampp SQL/APACHE to host a sql backend which I connect to an access front end using linked tables.

I have an audit table that tracks new records, deleted records, and changes from a table, and will add a new row when any of these instances occur, adding a timestamp and a changetype (new, delt, or chg). I did this coding in VBA.

This leaves me with an audit table (which i will use to generate invoices).

I want to use the ROW_NUMBER() function to rank records with the same ID by date, and then I will select the record with the highest rank to create a view with the most recent records. However, I cannot get ROW_NUMBER() to work in a query in my MySQL Workbench query editor, nor can I get it to work as a recordset declaration.

Does ROW_NUMBER() not work in this instance? I understand that it is a mysql server window function, but I figured that it would work in this implementation.

Thanks,

 

[Kheribus]

I'm doing a simple query like:

SELECT tripName, changeType, updateDate, ROW_NUMBER() OVER (ORDER BY updateDate DESC) FROM tripslog

also tried

SELECT tripName, changeType, updateDate, ROW_NUMBER() OVER (ORDER BY updateDate DESC) AS rankTable FROM tripslog

and also many combo's of this type of query, some using partition by as well.

the parser is giving me a parenthesis error...

 

[jdraw]

I don't use MYSQL but found this via google
http://stackoverflow.com/questions/...-the-equivalent-of-oracles-analytic-functions

and this http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/

and this
https://gist.github.com/kauragist/4208992

 

[Kheribus]

Oh snap... OK. I was very worried that this wouldn't work.

This is going to make things difficult. I'll do some homework and post back.

Thanks very much for the help.

 

[Kheribus]

Thanks for pointing me in the right direction.

The solution is:

SELECT *
FROM table AS t0
LEFT JOIN table AS t1 on t0.col1=t1.col1 AND t1.col2>t0.col2 
WHERE t1.col1 is NULL

This table has a composite key of col1 and col2 (id and date).