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

723563 · Mar 12, 2010 6:30PM

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 · Mar 12, 2010 7:29PM

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 · Mar 12, 2010 6:46PM

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 · Mar 12, 2010 6:49PM

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 · Mar 12, 2010 6:53PM

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 · Mar 12, 2010 7:10PM

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 · Mar 12, 2010 7:29PM

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 · Mar 12, 2010 7:51PM

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

Forum Stats

Quick Links

Categories

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

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Best Answer

Answers