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

723563

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

MichaelS

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.

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.

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

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

MichaelS

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.

723563

Thanks..I would like to go with this one.