I have the following Derby SQL statement which is embedded in my Java code and is executed via JDBC. It works, but it gives me all service dates. What I need is to refine this query so that it only gives me a tuple with the most-recent service-date. Can someone help me with this?
first I don't think LIMIT is what you're searching for. I don't know Derby SQL, but from a look to the documentation I would say, that limit just limits the number of rows which are returned.
But you do not want to exclude some rows from the result, you know exactly what you want.
Your nested solution didn't work, because you added an additional condition on table customers instead of customer_hist_lines.
But what you really want is to find the newest entry in customer_hist_lines.
Besides: you are looking for the entry with the largest value for year. If you do have more entries per year for a customer you will have to compose a real date out of day/month/year and find the maximum for this.
I changed the "=" to an "in" since it's guaranteed to be distinct. I added concatentation to make a full date (concat operators vary by database - I don't know Derby's offhand. Note this will only work if SERVICE_MONTH and SERVICE_DAY are always two digits. If not, you'll have to use the to_date function as described above.