How can I modify this query to obtain only the last record having a field value different from 0?

Question

I have this simple query:

SELECT *  
FROM   RendimentiGS  
WHERE  FondoID = 'ISPAI'  
ORDER BY DataRiferimento desc

Which returns a set of records like this:

ID      FondoID  DataRiferimento        Rendimento
1659    XXXXX    2016-01-01 00:00:00    0.00000
1658    XXXXX    2015-12-01 00:00:00    0.00000
1657    XXXXX    2015-11-01 00:00:00    0.00000
1656    XXXXX    2015-10-01 00:00:00    0.00000
1655    XXXXX    2015-09-01 00:00:00    2.71000
1654    XXXXX    2015-08-01 00:00:00    2.85000
1653    XXXXX    2015-07-01 00:00:00    3.00000
1652    XXXXX    2015-06-01 00:00:00    3.04000

These records are ordered based on the DataRiferimento field.

As you can see, from a specific data the value of the Rendimento field will have always the value of 0.

I want to obtain the last record that have a value different from 0.

So, in the previous example I need to obtain this specific record:

1655    XXXXX    2015-09-01 00:00:00    2.71000

I have modified the previous query in this way:

select * from RendimentiGS 
where RendimentiGS.FondoID = 'ISPAI' 
and Rendimento > 0
order by DataRiferimento desc

but it seems that it is not correct because in this way I obtain the list of all records having the Rendimento value >0 but not only the last one.

How can I solve this problem and obtain only the desired record?

Answer 1

So your query is correct if it would only return the first row ? If that is the case than try this :

select top 1
       * 
from   RendimentiGS 
where  RendimentiGS.FondoID = 'ISPAI' 
and    Rendimento > 0
order by DataRiferimento desc