Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
SQL Query in Getting Latest two records and to make it as single record

723563
Member Posts: 59
Hi Gurus,
My Oracle Version
My Desired Output:
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
-
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
-
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. -
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
-
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? -
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
-
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.
-
Thanks..I would like to go with this one.
This discussion has been closed.