AndreaNobili AndreaNobili - 1 month ago 7
SQL Question

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

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

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