how can get five days [message #666424] |
Fri, 03 November 2017 11:16 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/043a1274cb670fd010565969fc56603d?s=64&d=mm&r=g) |
hassan08
Messages: 122 Registered: June 2011 Location: egypt
|
Senior Member |
|
|
i need query to show only 5 dates
exanple
i have date of the table like this
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
20/2/2017
6/1/2015
7/1/2017
i want only show this dates
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
also show only 5 dates with right sequence
|
|
|
|
|
Re: how can get five days [message #666430 is a reply to message #666424] |
Fri, 03 November 2017 11:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/043a1274cb670fd010565969fc56603d?s=64&d=mm&r=g) |
hassan08
Messages: 122 Registered: June 2011 Location: egypt
|
Senior Member |
|
|
CREATE TABLE SCOTT.TEST1
(
P_1 NUMBER,
P_2 DATE
)
CREATE TABLE SCOTT.TEST1
(
Insert into TEST1
(P_1, P_2)
Values
(1, TO_DATE('01/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(2, TO_DATE('01/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(1, TO_DATE('01/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(3, TO_DATE('01/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(1, TO_DATE('01/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(1, TO_DATE('01/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(1, TO_DATE('01/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(3, TO_DATE('01/25/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(P_1, P_2)
Values
(2, TO_DATE('01/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
)
i want only show 5 dates for every month
|
|
|
|
|
|
|
|
|
|
Re: how can get five days [message #666454 is a reply to message #666451] |
Mon, 06 November 2017 02:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/681242c81f47835ede3fe01762c93acb?s=64&d=mm&r=g) |
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
If you only look at P_1 then it could be something like:SELECT *
FROM (SELECT DISTINCT P_2
FROM TEST1)
WHERE ROWNUM < 6
ORDER BY P_2;
If you need the first 5 dates for each P_1 then this should work:SELECT P_1, P_2
FROM (SELECT P_1, P_2, ROW_NUMBER() OVER(PARTITION BY P_1 ORDER BY P_2) AS RN FROM TEST1)
WHERE RN < 6
ORDER BY P_1, P_2;
|
|
|