how can get five days [message #666424] |
Fri, 03 November 2017 11:16 |
|
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 |
|
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 |
|
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;
|
|
|