aspose file tools*
The moose likes JDBC and the fly likes Stucked in group by !! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stucked in group by !!" Watch "Stucked in group by !!" New topic
Author

Stucked in group by !!

Dhiraj Srivastava
Ranch Hand

Joined: Aug 29, 2001
Posts: 49
Hi,

My table test has three columns

id partner_idDate
11025/10/1968
21025/10/1992
32015/11/1968
42015/11/1969
52015/11/1975
63022/12/1994
73023/10/1968
84025/10/1968


I want distinct record on the basis of partner id whose date is maximum

The result should be ::

id partner_idDate

21025/10/1992
52015/11/1975
63022/12/1994
84025/10/1968

Thanks in advance...

Dhiraj
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321

have you tried DISTINCT keyword:

DISTINCT clause allows you to remove duplicates from the result set.


Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 852
distinct won't work in this case as all rows of the original set are distinct. A correlated subquery like the one below may work. I don't know if the syntax is right, but it is close.

select id,partner_id, date from mytable t1 where exists (
select * from mytable t2 where t1.partner_id=t2.partner_id and t1.date=max(t2.date))


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Dhiraj Srivastava
Ranch Hand

Joined: Aug 29, 2001
Posts: 49
Its Not working !!!
Mathias Nilsson
Ranch Hand

Joined: Aug 21, 2004
Posts: 367
If you want the id to be shown use this else just use the subquery



// Mathias


SCJP1.4
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 852
Its Not working !!!>>

You would get better help if you would post more meaningful questions and comments. The above wasn't even worth the time to post.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1740
    
    2
Here's the classic way of doing this:



This version is a little cleaner than Mathias's version with the inline view.

Here's an old explanation of this.

And partway down in this thread, I give some reasons for favoring the correlated subquery approach over a "group by" approach.
[ April 04, 2007: Message edited by: Michael Matola ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stucked in group by !!
 
Similar Threads
Exception in struts Application
Problem in using date in WHERE clause in SELECT query in Servlet.
Generate random id on an html page
Classic Movies/Novels
I am unable to find error in this program.