• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

I Need Help With a Derby SQL Join Statement

 
Kaydell Leavitt
Ranch Hand
Posts: 689
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34207
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kaydell Leavitt
Ranch Hand
Posts: 689
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne, I'm going to try the nested-query.
 
Kaydell Leavitt
Ranch Hand
Posts: 689
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Kaydell Leavitt
Ranch Hand
Posts: 689
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
John Bengler
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34207
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic