aspose file tools*
The moose likes JDBC and the fly likes I Need Help With a Derby SQL Join Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "I Need Help With a Derby SQL Join Statement" Watch "I Need Help With a Derby SQL Join Statement" New topic
Author

I Need Help With a Derby SQL Join Statement

Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

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

Joined: May 26, 2003
Posts: 30929
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

Thanks Jeanne, I'm going to try the nested-query.
Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

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

Joined: Nov 18, 2006
Posts: 689

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

Joined: Feb 12, 2009
Posts: 133
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

Joined: May 26, 2003
Posts: 30929
    
158

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]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: I Need Help With a Derby SQL Join Statement