Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Query in Getting Latest two records and to make it as single record

723563Mar 12 2010 — edited Mar 12 2010
Hi Gurus,

My Oracle Version 
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production          
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE	11.1.0.7.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production                         
NLSRTL Version 11.1.0.7.0 - Production                                          

5 rows selected.
SQL> WITH T AS
  2  (
  3      SELECT 1 ID,'Personal' NAME,2 TYPE,TO_DATE('03/01/2010','MM/DD/YYYY') DT FROM DUAL UNION ALL
  4      SELECT 2,'Personal',2,TO_DATE('02/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
  5      SELECT 3,'Personal',2,TO_DATE('05/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
  6      SELECT 4,'Shared',1,TO_DATE('04/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
  7      SELECT 5,'Shared',1,TO_DATE('03/01/2010','MM/DD/YYYY')  FROM DUAL
  8  )SELECT * FROM T;

        ID NAME           TYPE DT
---------- -------- ---------- ---------
         1 Personal          2 01-MAR-10
         2 Personal          2 01-FEB-10
         3 Personal          2 01-MAY-10
         4 Shared            1 01-APR-10
         5 Shared            1 01-MAR-10

SQL>
I need to get the Latest Record from that data group by each Type and to get it as a single record.

My Desired Output:
        ID NAME           TYPE DT      SHARED_ID SHARED_NAME SHARED_DT
---------- -------- ---------- ------------------- -------- ---------- ---------
         3 Personal          2 01-MAY-10         4 Shared            1 01-APR-10
This post has been answered by MichaelS on Mar 12 2010
Jump to Answer

Comments

Frank Kulash
Hi,

The are several ways to get those results.

If you know there will always be 2 types, and you need 3 columns from each one, then I think a self-join is the best way: 
WITH	got_r_num	AS
(
	SELECT	id, name, type, dt
	,	ROW_NUMBER () OVER ( PARTITION BY  type
			      	     ORDER BY  	   dt	DESC
				   ) AS r_num
	FROM	t
--	WHERE	...	-- If you want any filtering, put it here
)
SELECT	p.id,		    p.name,		    p.dt
,	s.id AS shared_id,  s.name AS shared_name,  s.dt  AS shared_dt
FROM	got_r_num	    p
JOIN	got_r_num	    s	ON	p.r_num	= s.r_num
WHERE	p.type	= 2
AND	s.type	= 1
AND	p.r_num	= 1
;
With so few columns, the aggregate LAST function is also a viable alternative. (In general, self-joins are used more often than the aggregate FIRST and LAST functions, so I suggest you get comfortable with this technique first.)
If there were several types, Pivot might be better.
If the number of types was variable or unknown, then String Aggregation is best.
1 person found this helpful
Aketi Jyuuzou
It is Interesting question ;-)
WITH T AS(
SELECT 1 ID,'Personal' NAME,2 TYPE,TO_DATE('03/01/2010','MM/DD/YYYY') DT FROM DUAL UNION ALL
SELECT 2,'Personal',2,TO_DATE('02/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 3,'Personal',2,TO_DATE('05/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 4,'Shared',1,TO_DATE('04/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 5,'Shared',1,TO_DATE('03/01/2010','MM/DD/YYYY')  FROM DUAL)
select
max(ID) Keep(Dense_Rank Last order by decode(NAME,'Personal',DT) nulls first) as ID,
max(NAME) Keep(Dense_Rank Last order by decode(NAME,'Personal',DT) nulls first) as NAME,
max(TYPE) Keep(Dense_Rank Last order by decode(NAME,'Personal',DT) nulls first) as TYPE,
max(decode(NAME,'Personal',DT)) as DT,
max(ID) Keep(Dense_Rank Last order by decode(NAME,'Shared',DT) nulls first) as SHARED_ID,
max(NAME) Keep(Dense_Rank Last order by decode(NAME,'Shared',DT) nulls first) as SHARED_NAME,
max(TYPE) Keep(Dense_Rank Last order by decode(NAME,'Shared',DT) nulls first) as SHARED_TYPE,
max(decode(NAME,'Shared',DT)) as SHARED_DT
  from T;

ID  NAME      TYPE  DT        SHARED_ID  SHARED_N  SHARED_TYPE  SHARED_D
--  --------  ----  --------  ---------  --------  -----------  --------
 3  Personal     2  10-05-01          4  Shared              1  10-04-01
1 person found this helpful
723563
Thanks for your reply.

Actually, I need to query from a view the script of view looks so huge.

When i query it from a view, i will get that records and i am sure it will be just two types but not exactly three columns but that is fixed one (not dynamic).

Can you suggest me how to work out this?
Aketi Jyuuzou
We can use Pivot B-)
WITH T AS(
SELECT 1 ID,'Personal' NAME,2 TYPE,TO_DATE('03/01/2010','MM/DD/YYYY') DT FROM DUAL UNION ALL
SELECT 2,'Personal',2,TO_DATE('02/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 3,'Personal',2,TO_DATE('05/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 4,'Shared',1,TO_DATE('04/01/2010','MM/DD/YYYY')  FROM DUAL UNION ALL
SELECT 5,'Shared',1,TO_DATE('03/01/2010','MM/DD/YYYY')  FROM DUAL)
select *
  from t
pivot (max(ID) Keep(Dense_Rank Last order by DT) as ID,
       max(NAME) Keep(Dense_Rank Last order by DT) as Name,
       max(TYPE) Keep(Dense_Rank Last order by DT) as Type,
       max(dt) as dt for Name in ('Personal' as P,'Shared' as S));

P_ID  P_NAME    P_TYPE  P_DT      S_ID  S_NAME    S_TYPE  S_DT
----  --------  ------  --------  ----  --------  ------  --------
   3  Personal       2  10-05-01     4  Shared         1  10-04-01
1 person found this helpful
MichaelS
Answer
Or
SQL> with t as
(
 select 1 id,'Personal' name,2 type,to_date('03/01/2010','MM/DD/YYYY') dt from dual union all
 select 2,'Personal',2,to_date('02/01/2010','MM/DD/YYYY')  from dual union all
 select 3,'Personal',2,to_date('05/01/2010','MM/DD/YYYY')  from dual union all
 select 4,'Shared',1,to_date('04/01/2010','MM/DD/YYYY')  from dual union all
 select 5,'Shared',1,to_date('03/01/2010','MM/DD/YYYY')  from dual
)
--
--
select max (decode (rownum, 1, id)) id,
       max (decode (rownum, 1, name)) name,
       max (decode (rownum, 1, type)) type,
       max (decode (rownum, 1, dt)) dt,
       max (decode (rownum, 2, id)) s_id,
       max (decode (rownum, 2, name)) s_name,
       max (decode (rownum, 2, type)) s_type,
       max (decode (rownum, 2, dt)) s_dt
from (select t.*, max (dt) over (partition by name) m_dt from t)
where dt = m_dt
/
        ID NAME           TYPE DT               S_ID S_NAME       S_TYPE S_DT      
---------- -------- ---------- ---------- ---------- -------- ---------- ----------
         3 Personal          2 05/01/2010          4 Shared            1 04/01/2010
1 row selected.
Marked as Answer by 723563 · Sep 27 2020
723563
Thanks..I would like to go with this one.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 9 2010
Added on Mar 12 2010
6 comments
5,394 views