I Need Help With a Derby SQL Join Statement

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?

Thank you for the time to read my question

Kaydell,
How about a nested query? Do you know how to write a query that returns the id of the record with the most recent service date? You could stick this inside the main query to control the id.

Alternatively, you could add an order by to your query and just get the first row.

Thanks Jeanne, I'm going to try the nested-query.

Here's my attempt at the nested query. It gives me an SQL-syntax error: "java.sql.SQLSyntaxErrorException: Invalid use of an aggregate function."

I believe that the problem is that the function called "max()" isn't used properly, but I don't understand how to specify that I want the query only to yield the tuple with the latest year.

Here's my new query:

// select the data to appear in the customer-search window
		String sql = 
			"SELECT " +
			"CUSTOMERS.RECORD_ID, " +
			"CUSTOMER_HIST_LINES.SERVICE_YEAR AS YYYY, " +
			"CUSTOMER_HIST_LINES.SERVICE_MONTH AS MM, " +
			"CUSTOMER_HIST_LINES.SERVICE_DAY AS DD, " +
			"CUSTOMERS.HOUSE, " +
			"CUSTOMERS.STREET, " +
			"CUSTOMERS.CITY_NAME, " +
			"CUSTOMERS.LAST_NAME_1, " +
			"CUSTOMERS.FIRST_NAME_1, " +
			"CUSTOMERS.FIRST_NAME_2, " +
			"CUSTOMERS.PHONE_1, " +
			"CUSTOMERS.SPRINKLERS, " +
			"CUSTOMERS.FACES, " +
			"CUSTOMERS.PRICE, " +
			"CUSTOMERS.FREQUENCY, " +
			"CUSTOMERS.COMMENTS, " +
			"CUSTOMERS.PHONE_2, " +
			"CUSTOMERS.EMAIL, " +
			"CUSTOMERS.ZIP_CODE, " +
			"CUSTOMERS.LAST_NAME_2, " +
			"CUSTOMERS.SUBDIVISION, " +
			"CUSTOMERS.STATE " +
			"FROM CUSTOMERS " +
			"LEFT JOIN CUSTOMER_HIST_LINES " +
			"ON CUSTOMERS.RECORD_ID = CUSTOMER_HIST_LINES.CUSTOMER_RECORD_ID " +
			"WHERE CUSTOMERS.RECORD_ID = (SELECT CUSTOMERS.RECORD_ID FROM CUSTOMERS " + 
			                                                       "WHERE SERVICE_YEAR = MAX(SERVICE_YEAR))";

OK, so should I try to use a LIMIT?

Here's a link to the Derby DB documentation:
http://db.apache.org/derby/faq.html#limit

I'm not sure what to do at this point.

Hi Kaydell,

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 hope this will help you..

John

You are close:

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.

[edited to fix code tags]