Forum Stats

  • 3,838,627 Users
  • 2,262,385 Discussions
  • 7,900,712 Comments

Discussions

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

723563
723563 Member Posts: 59
edited Mar 12, 2010 7:51PM in SQL & PL/SQL
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

Best Answer

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Mar 12, 2010 6:53PM
    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.
    Frank Kulash
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    Aketi Jyuuzou
  • 723563
    723563 Member Posts: 59
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    Aketi Jyuuzou
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
  • 723563
    723563 Member Posts: 59
    Thanks..I would like to go with this one.
This discussion has been closed.