• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Stucked in group by !!

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1325
Android Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
have you tried DISTINCT keyword:

DISTINCT clause allows you to remove duplicates from the result set.
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its Not working !!!
 
Ranch Hand
Posts: 367
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic