selecting distinct or max value?

[tyutghf]

I have a table that lists items, there is a 'islive' row that is set to 1 if the row should be visible on the website, null if not.

When a row is updated the previous entry 'islive' is set to null and a new row created so that I have an audit history of that record over time.

When a row is removed 'islive' is simply set to null.

Due to this format, one record will have many rows with subtle changes, all set to 'islive' = null.

i.e.

id - fullname - someothervalue - islive
1 - bob - sdjhj - null
2 - bob - dfhdfhjd - null
3 - jane - dsjfhjsh fjsdhj - null
4 - bob - dhf - null
5 - jane - hh - null


I am trying to write a SQL statement that will bring back the last record of each distinct fullname field where islive = null.

In the above data I would want to output

4 - bob - dhf - null
5 - jane - hh - null

as they were the last entries of each fullname value.

Been playing aroudn with distinct (the someothervalue fields being different are retruning multiple bobs and janes) and max() but having no luck here.

Can someone help?

Thanks

 

[djj55]

SELECT a.id, a.fullname, ..., a.islive 
from mytable a 
inner join (
SELECT MAX(id), fullname 
FROM mytable 
WHERE islive IS NULL
GROUP BY fullname
) as b 
on a.id = b.id

or something like this (not tested)

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!