Home » SQL & PL/SQL » SQL & PL/SQL » Query to get highest date id's (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production, Linux)
| Query to get highest date id's [message #624087] |
Wed, 17 September 2014 11:12  |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
Hi All,
I have below query using with clause which produces below result set
with ddtls as
(SELECT 1 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
--
SELECT 2 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140710' AS RUN_DATE FROM DUAL-- UNION ALL
)
select * from (
select * from
(select id, RUN_DATE,dense_rank() over(partition by id order by RUN_DATE desc) rnk from ddtls)
)
WHERE to_char(to_date(RUN_DATE,'yyyymmdd'),'dd-mon-yy')
BETWEEN to_char(to_date('20140701','yyyymmdd'),'dd-mon-yy') AND to_char(to_date('20140905','yyyymmdd'),'dd-mon-yy');
/*Below is the result set
ID RUN_DATE RANK
--------------------------------------
1 20140704 5
1 20140703 6
1 20140702 7
2 20140704 6
2 20140703 7
2 20140702 8
*/
I need result set like below as per date passed in where clause
ID RUN_DATE RANK
--------------------------------------
1 20140704 5
2 20140704 6
Please help me to get above result set
Thanks,
Anil
Edited by Lalit : Added code tags to the output mentioned by OP.
[Updated on: Thu, 18 September 2014 00:35] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query to get highest date id's [message #624093 is a reply to message #624092] |
Wed, 17 September 2014 13:04  |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
Thanks Lalit and Michel for the reply.
I have a below query which produces the result set, are there any other approaches? Please suggest
with ddtls as
(SELECT 1 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
SELECT 1 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
--
SELECT 2 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
SELECT 2 as id, '20140710' AS RUN_DATE FROM DUAL-- UNION ALL
)
select id, MAX(RUN_DATE) run_date from (
select * from
(select id, RUN_DATE,dense_rank() over(partition by id order by RUN_DATE desc) rnk from ddtls)
)
WHERE to_char(to_date(RUN_DATE,'yyyymmdd'),'dd-mon-yy')
BETWEEN to_char(to_date('20140701','yyyymmdd'),'dd-mon-yy') AND to_char(to_date('20140905','yyyymmdd'),'dd-mon-yy')
group by id;
Thanks,
Anil MK
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query to get highest date id's [message #624118 is a reply to message #624107] |
Wed, 17 September 2014 23:11 |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
Thank you all for the reply.
Where clause in my query is
WHERE to_char(to_date(RUN_DATE,'yyyymmdd'),'dd-mon-yy')
BETWEEN to_char(to_date('20140701','yyyymmdd'),'dd-mon-yy') AND to_char(to_date('20140905','yyyymmdd'),'dd-mon-yy')
/*In above where clause passing date as 1-Jul-2014 to 05-Jul-2014, between those dates I need latest date record for each id
ID RUN_DATE RANK
--------------------------------------
1 20140704 5
2 20140704 6
*/
So I am getting result set as above, here rank is not required to display. Are there any other approaches for the same? Please suggest.
Thanks,
Anil MK
|
|
|
|
|
|
|
|
| Re: Query to get highest date id's [message #624127 is a reply to message #624118] |
Thu, 18 September 2014 00:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
anil_mk wrote on Thu, 18 September 2014 09:41In above where clause passing date as 1-Jul-2014 to 05-Jul-2014, between those dates I need latest date record for each id
ID RUN_DATE RANK
--------------------------------------
1 20140704 5
2 20140704 6
See this :
SQL> with ddtls as
2 (SELECT 1 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
3 SELECT 1 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
4 SELECT 1 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
5 SELECT 1 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
6 SELECT 1 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
7 SELECT 1 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
8 SELECT 1 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
9 --
10 SELECT 2 as id, '20140709' AS RUN_DATE FROM DUAL UNION ALL
11 SELECT 2 as id, '20140708' AS RUN_DATE FROM DUAL UNION ALL
12 SELECT 2 as id, '20140707' AS RUN_DATE FROM DUAL UNION ALL
13 SELECT 2 as id, '20140706' AS RUN_DATE FROM DUAL UNION ALL
14 SELECT 2 as id, '20140704' AS RUN_DATE FROM DUAL UNION ALL
15 SELECT 2 as id, '20140703' AS RUN_DATE FROM DUAL UNION ALL
16 SELECT 2 as id, '20140702' AS RUN_DATE FROM DUAL UNION ALL
17 SELECT 2 as id, '20140710' AS RUN_DATE FROM DUAL-- UNION ALL
18 )
19 SELECT *
20 FROM (SELECT id,
21 run_date,
22 rnk,
23 Row_number()
24 over(
25 PARTITION BY id
26 ORDER BY To_date(run_date, 'yyyymmdd') DESC) max_dt_rnk
27 FROM (SELECT id,
28 To_char(To_date(run_date, 'yyyymmdd'), 'yyyymmdd')
29 run_date,
30 Dense_rank()
31 over(
32 PARTITION BY id
33 ORDER BY To_date(run_date, 'yyyymmdd') DESC) RNK
34 FROM ddtls)
35 WHERE To_date(run_date, 'yyyymmdd') BETWEEN
36 To_date('20140701', 'yyyymmdd') AND
37 To_date('20140705', 'yyyymmdd'))
38 WHERE max_dt_rnk = 1
39
SQL> /
ID RUN_DATE RNK MAX_DT_RNK
---------- -------- ---------- ----------
1 20140704 5 1
2 20140704 6 1
SQL>
Regards,
Lalit
[Updated on: Thu, 18 September 2014 00:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query to get highest date id's [message #624156 is a reply to message #624149] |
Thu, 18 September 2014 10:04 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
I agree with Michel, since my solution is based on OP's limited information which I had to understand by analyzing his attempts and desired output. A complete test case would always give more opportunity for a better solution. I was expecting further clarification from OP, but he ended up with "thanks".
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 06:22:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
where?