Selecting a column based on DATE along with GROUP BY

Database
Enthusiast

Selecting a column based on DATE along with GROUP BY

Hi All,

I have a below requirement:-

I have data as below

GID - MID - UID - DATE
1000000 - 1000001 - 9000001 - 2013-11-01
1000000 - 1000002 - 9000002 - 2013-11-21
1000000 - 1000003 - 9000003 - 2013-11-11
1000000 - 1000004 - 9000004 - 2013-11-12

I want to assign GID as UID with highest DATE to all MIDs group by GID.

In this case, UID 9000002 is having highest DATE and this should be assigned as GID.

Hence, my output should be looks like below:-

GID - MID
9000002 - 1000001
9000002 - 1000002
9000002 - 1000003
9000002 - 1000004

I know that this can be achieved by selecting UID of highest DATE and join to the original table to assign it as GID.

Is there any other way this can be achieved in a simplest way.

Thanks a lot in advance.

Sagar.
2 REPLIES
Enthusiast

Re: Selecting a column based on DATE along with GROUP BY

Hi Sagar,

You mean to say you dont want this way below?

select abc.u_id as gid,dt.mid from
(SELECT MAX(date1) over (partition by gid order by gid) dt1,gid,mid,u_id FROM ABC) dt, abc
where abc.date1=dt.dt1 order by 2

or is okay?

Cheers,

Raja

Enthusiast

Re: Selecting a column based on DATE along with GROUP BY

Hi,

You can try this

SELECT (SEL UID FROM TD3471.TEMP WHERE dt=(SEL MAX(dt) FROM TD3471.TEMP )) AS GID, MID
FROM TD3471.TEMP
ORDER BY 2;
Khurram