Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stucked in group by !!

 
Dhiraj Srivastava
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1324
Android Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
have you tried DISTINCT keyword:

DISTINCT clause allows you to remove duplicates from the result set.
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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))
 
Dhiraj Srivastava
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its Not working !!!
 
Mathias Nilsson
Ranch Hand
Posts: 367
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want the id to be shown use this else just use the subquery



// Mathias
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1820
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic