Stucked in group by !!

[Dhiraj Srivastava]

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]

have you tried DISTINCT keyword:

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

[steve souza]

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]

Its Not working !!!

[Mathias Nilsson]

If you want the id to be shown use this else just use the subquery

SELECT m.* FROM Mytable m, ( SELECT Max( Date ) d , partner_id FROM
MyTable GROUP BY partner_id ) as a
WHERE m.partner_id = a.partner_id and m.Date = a.d

// Mathias

[steve souza]

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]

Here's the classic way of doing this:

select t1.* from
test t1
 where t1.date = (select max(t2.date)
 from test t2
 where t2.partner_id = t1.partner_id)

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 ]