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 Spring in Action this week in the Spring 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: 861
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: 861
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: 1751
    
    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 !!