Not sure if I understand the requirement correctly but from what I gather you first need to find the rows for each empid containing max based on date2 and then from this set the rows having max of date1 per each empid. If this was correct interpretation, you could consider something like this
First the example data create table ta1 (
empid int,
name varchar(10),
date1 date,
date2 date
);
insert into ta1 (EmpID, Name, Date1, Date2) values
(1, 'A', '30/Jan/2021', '20/Jan/2021'),
(1, 'A', '29/Jan/2021', '20/Jan/2021'),
(2, 'B', Null, '20/Jan/2021'),
(2, 'B', NUll, '21/Jan/2021'),
(3, 'C', '28/Jan/2021', '21/Jan/2021'); Now let's get the rows having max date2 for each emp. The query could look like this WITH Date2Query AS (
SELECT t1.empid, t1.name,
t1.date1,
t1.date2
FROM ta1 T1
WHERE t1.date2 = (select max(t2.date2) from ta1 T2
where t1.name = t2.name)
)
SELECT * FROM Date2Query;so the result would be empid name date1 date2
3 C 2021-01-28 2021-01-21
2 B NULL 2021-01-21
1 A 2021-01-30 2021-01-20
1 A 2021-01-29 2021-01-20 Now to get the rows for each emp having max of date1 you can use the approach you already tried but in case of null, use a non-existent date in the comparison to ensure that if max is null then the comparison is true. For example using COALESCE the query could look like this WITH Date2Query AS (
SELECT t1.empid, t1.name,
t1.date1,
t1.date2
FROM ta1 T1
WHERE t1.date2 = (SELECT max(t2.date2) from ta1 T2
where t1.name = t2.name)
)
SELECT t1.empid, t1.name,
t1.date1
FROM Date2Query T1
WHERE COALESCE(t1.date1,'01/01/1900') = COALESCE((SELECT MAX(t2.date1)
FROM Date2Query T2
WHERE t1.name = t2.name),'01/01/1900') ;
And the result is empid name date1
3 C 2021-01-28
2 B NULL
1 A 2021-01-30 This can be re-written in several ways. One option is to fetch NULL's and non-NULL's separately, for example Place the previous row numbering statement in a subquery. Filter this to the N rows you want per customer in the outer query. Get the last three orders for each customer
with rws as (
select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o
)
select * from rws
where rn <= 3
order by customer_id, order_datetime desc
| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | -- | | ---- | - | | - | - | --- | ---- | - | | - | - | --- | ---- | - | | - | - | - | ---- | - | | - | - | | ---- | - | | - | - | --- | ---- | - | | - | - | --- | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | --- | ---- | - | | - | - | --- | ---- | - | | - | - | --- | ---- | - | | - | - | --- | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | --- | ---- | - | | - | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | --- | ---- | -- | | -- | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | --- | ---- | -- | | - | - | | ---- | -- | | - | - | --- | ---- | -- | | -- | - | --- | ---- | -- | | - | - | | ---- | -- | | -- | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | | ---- | -- | | - | - | | ---- | -- | | -- | - | | ---- | -- | | -- | - | | ---- | -- | | -- | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | | ---- | -- | | - | - | | ---- | -- | | -- | - | | ---- | -- | | - | - | --- | ---- | -- | | - | - |
| 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 1730 | 23-FEB-19 02.34.28.490023 AM | 10 | COMPLETE | 10 | 1 | 1525 | 21-JAN-19 01.17.54.579876 PM | 10 | COMPLETE | 1 | 2 | 986 | 22-OCT-18 05.59.46.862850 AM | 10 | COMPLETE | 10 | 3 | 1635 | 05-FEB-19 01.18.25.041366 PM | 11 | COMPLETE | 11 | 1 | 1321 | 19-DEC-18 05.28.34.329405 PM | 11 | COMPLETE | 1 | 2 | 960 | 17-OCT-18 08.14.25.241276 AM | 11 | COMPLETE | 1 | 3 | 1637 | 06-FEB-19 02.16.49.481784 AM | 12 | COMPLETE | 1 | 1 | 839 | 24-SEP-18 12.55.09.647618 AM | 12 | COMPLETE | 12 | 2 | 125 | 10-APR-18 12.41.39.268531 PM | 12 | COMPLETE | 1 | 3 | 1498 | 17-JAN-19 05.36.16.018250 AM | 13 | COMPLETE | 13 | 1 | 1418 | 05-JAN-19 04.26.48.901210 AM | 13 | COMPLETE | 13 | 2 | 1052 | 02-NOV-18 04.10.21.166613 AM | 13 | COMPLETE | 1 | 3 | 1560 | 25-JAN-19 05.33.55.537004 AM | 14 | COMPLETE | 1 | 1 | 1483 | 14-JAN-19 07.04.58.793222 PM | 14 | COMPLETE | 14 | 2 | 1471 | 13-JAN-19 06.25.29.562733 AM | 14 | COMPLETE | 14 | 3 | 1486 | 15-JAN-19 03.31.29.057027 PM | 15 | COMPLETE | 15 | 1 | 1400 | 02-JAN-19 05.48.13.206098 AM | 15 | COMPLETE | 15 | 2 | 1195 | 30-NOV-18 05.42.24.627921 PM | 15 | COMPLETE | 1 | 3 | 1451 | 10-JAN-19 07.06.48.689623 PM | 16 | COMPLETE | 16 | 1 | 1448 | 10-JAN-19 06.05.40.626338 PM | 16 | COMPLETE | 1 | 2 | 1439 | 08-JAN-19 03.48.25.232374 PM | 16 | COMPLETE | 1 | 3 | 1552 | 24-JAN-19 12.09.14.646254 PM | 17 | COMPLETE | 17 | 1 | 1200 | 30-NOV-18 11.19.54.798149 PM | 17 | COMPLETE | 1 | 2 | 899 | 06-OCT-18 08.37.49.467272 PM | 17 | COMPLETE | 1 | 3 |
Rows 1 - 50. More rows exist. - Statement 4
Get the date of the last order for each customer select customer_id, max ( order_datetime )
from co.orders
group by customer_id | CUSTOMER_ID | MAX(ORDER_DATETIME) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| --- | ---- | - | ------ | | ------ | - | ------ | | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ---- | - | ------ | - | ------ | | ------ | - | ------ | | ------ | - | ------ | - | ------ | | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ------ | - | ------ | | ------ | | ------ | - | ------ | - | ------ | | ------ | - | ------ | | ------ | | ------ | - | ------ | - | ------ | | ------ | - | ------ | - | ------ | | ------ | - | ------ | | ------ | - | ------ | - | ------ | - | ------ | | ------ | - | ------ | - | ---- | | ------ |
| 124 | 12-OCT-18 08.15.39.717974 PM | 107 | 13-MAR-19 04.10.34.107048 PM | 14 | 25-JAN-19 05.33.55.537004 AM | 166 | 25-MAR-19 02.52.16.631428 AM | 51 | 20-MAR-19 07.13.19.500719 PM | 281 | 19-MAR-19 06.19.55.352793 AM | 359 | 10-MAR-19 09.21.02.444428 AM | 318 | 21-MAR-19 03.07.31.851060 PM | 161 | 21-MAR-19 07.50.08.213950 PM | 237 | 07-MAR-19 02.59.15.029147 AM | 328 | 20-DEC-18 12.39.35.118770 PM | 6 | 10-JAN-19 06.52.30.324331 PM | 173 | 19-OCT-18 04.30.09.442692 PM | 391 | 18-MAR-19 06.50.34.800957 AM | 27 | 03-FEB-19 05.13.10.955291 AM | 135 | 12-MAR-19 05.09.38.031580 AM | 23 | 20-MAR-18 12.07.13.591684 AM | 226 | 02-FEB-19 08.28.14.386011 AM | 348 | 12-APR-19 09.41.06.642641 PM | 58 | 31-MAR-19 06.29.01.038321 PM | 158 | 06-MAR-19 11.17.49.990617 PM | 108 | 04-MAR-19 05.36.39.891421 PM | 277 | 18-MAR-19 01.24.20.184511 AM | 291 | 26-AUG-18 07.25.53.889570 AM | 125 | 17-MAR-19 06.33.56.523112 AM | 371 | 24-MAR-19 05.06.14.312057 PM | 210 | 08-JUL-18 02.36.38.114618 PM | 72 | 06-FEB-19 04.33.54.085409 AM | 64 | 19-DEC-18 08.08.49.968261 PM | 313 | 16-NOV-18 08.44.20.739015 AM | 113 | 13-AUG-18 08.53.08.143929 AM | 57 | 07-JAN-19 03.05.40.406352 PM | 232 | 03-APR-19 01.40.01.840676 PM | 52 | 08-JAN-19 12.50.52.374027 PM | 50 | 14-MAR-19 02.07.24.615426 AM | 290 | 08-APR-19 11.16.55.199255 PM | 189 | 05-JAN-19 06.57.32.486289 AM | 71 | 16-FEB-19 04.51.59.801488 AM | 136 | 05-APR-19 12.26.49.495590 AM | 344 | 07-MAR-19 06.11.57.595424 PM | 85 | 20-DEC-18 01.53.48.432922 AM | 206 | 31-JAN-19 04.04.00.853750 PM | 88 | 22-SEP-18 07.44.46.434859 PM | 350 | 08-OCT-18 08.13.06.842274 AM | 268 | 10-NOV-18 02.47.04.331650 PM | 180 | 20-FEB-19 04.02.05.809720 PM | 40 | 16-DEC-18 11.47.49.807137 AM | 109 | 30-MAR-19 01.37.15.392243 AM | 7 | 25-MAR-19 11.31.58.636926 PM | 99 | 30-DEC-18 04.09.22.336203 PM |
Rows 1 - 50. More rows exist. - Statement 5
Get the date of the last order for each customer and the store where this was made select customer_id, max ( order_datetime ),
max ( store_id ) keep (
dense_rank last
order by order_datetime
) last_purchase_store_id
from co.orders
group by customer_id | CUSTOMER_ID | MAX(ORDER_DATETIME) | LAST_PURCHASE_STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| --- | ---- | -- | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | | ------ | | | ---- | | | ---- | - | | ------ | | | ---- | - | | ------ | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | | | ---- | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | | | ---- | | | ---- | - | | ------ | - | | ------ | | | ---- | | | ---- | - | | ------ | | | ---- | | | ---- | -- |
| 1 | 16-JAN-19 08.12.31.926232 AM | 1 | 2 | 09-SEP-18 08.32.41.319898 AM | 2 | 3 | 20-JAN-19 11.42.12.885651 PM | 1 | 4 | 13-JAN-19 01.22.50.411160 AM | 1 | 5 | 31-AUG-18 05.08.24.306909 AM | 1 | 6 | 10-JAN-19 06.52.30.324331 PM | 1 | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | 8 | 10-MAR-19 09.54.25.125750 PM | 8 | 9 | 10-DEC-18 12.17.10.374740 AM | 9 | 10 | 23-FEB-19 02.34.28.490023 AM | 10 | 11 | 05-FEB-19 01.18.25.041366 PM | 11 | 12 | 06-FEB-19 02.16.49.481784 AM | 1 | 13 | 17-JAN-19 05.36.16.018250 AM | 13 | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | 15 | 15-JAN-19 03.31.29.057027 PM | 15 | 16 | 10-JAN-19 07.06.48.689623 PM | 16 | 17 | 24-JAN-19 12.09.14.646254 PM | 17 | 18 | 10-FEB-19 01.15.53.995472 AM | 18 | 19 | 23-FEB-19 08.53.32.461195 PM | 19 | 20 | 09-MAR-19 10.33.24.254670 AM | 20 | 21 | 12-MAR-19 11.56.53.384122 PM | 21 | 22 | 11-MAR-19 07.20.17.942291 AM | 22 | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | 24 | 16-AUG-18 05.47.22.306204 PM | 1 | 25 | 18-OCT-18 09.52.33.531823 PM | 1 | 26 | 21-JAN-19 11.28.57.135392 AM | 1 | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | 28 | 01-FEB-19 10.59.58.576087 PM | 1 | 29 | 27-MAR-19 09.29.34.311413 PM | 23 | 30 | 31-JAN-19 08.32.37.958480 PM | 1 | 31 | 20-DEC-18 11.19.17.528223 AM | 2 | 32 | 11-NOV-18 04.36.12.942269 AM | 1 | 33 | 20-DEC-18 06.10.04.750197 PM | 1 | 34 | 26-SEP-18 04.29.39.472854 AM | 1 | 35 | 05-DEC-18 07.20.08.753049 AM | 6 | 36 | 15-JAN-19 10.51.20.451280 PM | 7 | 37 | 25-SEP-18 02.00.27.715942 PM | 8 | 38 | 03-DEC-18 06.47.29.874991 PM | 9 | 39 | 27-DEC-18 03.04.20.388274 PM | 1 | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | 41 | 10-JAN-19 09.11.04.094560 PM | 12 | 42 | 01-DEC-18 07.42.04.777939 PM | 13 | 43 | 23-JAN-19 09.03.54.597996 PM | 1 | 44 | 25-JAN-19 03.44.54.626100 AM | 1 | 45 | 17-FEB-19 06.37.31.277529 PM | 16 | 46 | 16-DEC-18 09.48.49.073184 AM | 17 | 47 | 03-FEB-19 10.09.23.422797 AM | 1 | 48 | 26-FEB-19 05.05.37.459393 AM | 19 | 49 | 15-MAR-19 03.50.48.032274 AM | 20 | 50 | 14-MAR-19 02.07.24.615426 AM | 21 |
Rows 1 - 50. More rows exist. - Statement 6
This changes the previous query, switching last for first and ascending sort to desceding, but gives the same result
Get the date of the last order for each customer and the store where this was made | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected. 0 | CUSTOMER_ID | MAX(ORDER_DATETIME) | LAST_PURCHASE_STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| - | ---- | -- | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | - | ---- | - | | ------ | | | ---- | | | ---- | - | | ------ | | | ---- | - | | ------ | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | | | ---- | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | | | ---- | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | - | | ------ | | | ---- | | | ---- | - | | ------ | - | | ------ | | | ---- | | | ---- | - | | ------ | | | ---- | | | ---- | -- |
| 1 | 16-JAN-19 08.12.31.926232 AM | 1 | 2 | 09-SEP-18 08.32.41.319898 AM | 2 | 3 | 20-JAN-19 11.42.12.885651 PM | 1 | 4 | 13-JAN-19 01.22.50.411160 AM | 1 | 5 | 31-AUG-18 05.08.24.306909 AM | 1 | 6 | 10-JAN-19 06.52.30.324331 PM | 1 | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | 8 | 10-MAR-19 09.54.25.125750 PM | 8 | 9 | 10-DEC-18 12.17.10.374740 AM | 9 | 10 | 23-FEB-19 02.34.28.490023 AM | 10 | 11 | 05-FEB-19 01.18.25.041366 PM | 11 | 12 | 06-FEB-19 02.16.49.481784 AM | 1 | 13 | 17-JAN-19 05.36.16.018250 AM | 13 | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | 15 | 15-JAN-19 03.31.29.057027 PM | 15 | 16 | 10-JAN-19 07.06.48.689623 PM | 16 | 17 | 24-JAN-19 12.09.14.646254 PM | 17 | 18 | 10-FEB-19 01.15.53.995472 AM | 18 | 19 | 23-FEB-19 08.53.32.461195 PM | 19 | 20 | 09-MAR-19 10.33.24.254670 AM | 20 | 21 | 12-MAR-19 11.56.53.384122 PM | 21 | 22 | 11-MAR-19 07.20.17.942291 AM | 22 | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | 24 | 16-AUG-18 05.47.22.306204 PM | 1 | 25 | 18-OCT-18 09.52.33.531823 PM | 1 | 26 | 21-JAN-19 11.28.57.135392 AM | 1 | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | 28 | 01-FEB-19 10.59.58.576087 PM | 1 | 29 | 27-MAR-19 09.29.34.311413 PM | 23 | 30 | 31-JAN-19 08.32.37.958480 PM | 1 | 31 | 20-DEC-18 11.19.17.528223 AM | 2 | 32 | 11-NOV-18 04.36.12.942269 AM | 1 | 33 | 20-DEC-18 06.10.04.750197 PM | 1 | 34 | 26-SEP-18 04.29.39.472854 AM | 1 | 35 | 05-DEC-18 07.20.08.753049 AM | 6 | 36 | 15-JAN-19 10.51.20.451280 PM | 7 | 37 | 25-SEP-18 02.00.27.715942 PM | 8 | 38 | 03-DEC-18 06.47.29.874991 PM | 9 | 39 | 27-DEC-18 03.04.20.388274 PM | 1 | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | 41 | 10-JAN-19 09.11.04.094560 PM | 12 | 42 | 01-DEC-18 07.42.04.777939 PM | 13 | 43 | 23-JAN-19 09.03.54.597996 PM | 1 | 44 | 25-JAN-19 03.44.54.626100 AM | 1 | 45 | 17-FEB-19 06.37.31.277529 PM | 16 | 46 | 16-DEC-18 09.48.49.073184 AM | 17 | 47 | 03-FEB-19 10.09.23.422797 AM | 1 | 48 | 26-FEB-19 05.05.37.459393 AM | 19 | 49 | 15-MAR-19 03.50.48.032274 AM | 20 | 50 | 14-MAR-19 02.07.24.615426 AM | 21 |
Rows 1 - 50. More rows exist. - Statement 7
Using the keep clause can enable you to fetch data which would have been three separate queries into one statement
Get the date of the last purchase, location of first purchase, and status of most recent order for each customer | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected. 2 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| -- | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | -- |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected.
3 - Statement 8
The any_value function efficiently picks a value from the group to return. This is non-deterministic, so may give different results.
Get the date of the last order and a random value for the other columns | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected. 4 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| -- | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | -- |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected.
5 - Statement 9
Many customers can make the same number of purchases from each location. So there can be duplicates count values for each store. This only stores by the count, so is non-deterministic.
Get the three customers that placed the most orders at each store | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected. 6 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| -- | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | -- |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected.
7 - Statement 10
This extends the order by in row_number() from the previous query to include customer_id. This makes the result deterministic.
Get the three customers that placed the most orders at each store | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected. 8 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| -- | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | -- |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected.
9 - Statement 11
This switches row_number from the previous queries to rank. This returns all customers that made the same number of orders as the third highest at each store
Get the customers that placed the most orders at each store select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o 0 select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 12
This uses dense_rank to return all the customers that made the same number of purchases as the three highest counts at each store.
Get the three highest of orders per store all the customers who made these number of purchases select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o 2 select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 13
This returns (up to) NUM_ROWS from the table passed
A SQL Macro to get N rows from a table select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 14
This will return (any) three rows from the table
Calling the top N SQL macro select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o 5 select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 15
To see the query generated by a SQL macro, pass it to dbms_utility.expand_sql_text
Get the SQL generated by the macro select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o 7 select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 16
This creates a top-N/group template; at runtime you can pass the table, partition, and sorting columns. By default it sorts all columns ascending; to change any to descending pass them in the list of desc_cols
A top-N per group SQL macro select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o - Statement 17
Get the last three orders for each customer using a SQL macro | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | -- | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ------ | - | | - | - | | ------ | - | | - | - | - | ---- | - | - | - | | -- | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - |
| 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 182 | 26-APR-18 03.56.02.680569 AM | 1 | COMPLETE | 1 | 4 | 159 | 20-APR-18 12.51.57.254596 PM | 1 | COMPLETE | 1 | 5 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 608 | 11-AUG-18 06.57.30.697703 PM | 3 | COMPLETE | 1 | 4 | 544 | 27-JUL-18 05.27.59.314441 AM | 3 | COMPLETE | 1 | 5 | 307 | 27-MAY-18 04.27.28.810727 AM | 3 | COMPLETE | 1 | 6 | 298 | 24-MAY-18 08.30.09.072186 PM | 3 | COMPLETE | 1 | 7 | 63 | 22-MAR-18 06.17.24.214468 AM | 3 | COMPLETE | 1 | 8 | 20 | 01-MAR-18 10.07.32.467335 AM | 3 | COMPLETE | 3 | 9 | 1 | 04-FEB-18 01.20.22.245677 PM | 3 | CANCELLED | 1 | 10 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 672 | 23-AUG-18 12.33.23.328945 PM | 4 | COMPLETE | 4 | 4 | 585 | 05-AUG-18 09.58.27.792726 PM | 4 | COMPLETE | 1 | 5 | 506 | 17-JUL-18 09.52.33.762499 PM | 4 | COMPLETE | 1 | 6 | 453 | 02-JUL-18 11.08.52.629924 PM | 4 | COMPLETE | 4 | 7 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 972 | 19-OCT-18 01.08.36.540797 PM | 6 | COMPLETE | 6 | 4 | 769 | 09-SEP-18 01.45.17.970634 PM | 6 | REFUNDED | 1 | 5 | 204 | 30-APR-18 11.39.15.779302 AM | 6 | COMPLETE | 1 | 6 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 950 | 15-OCT-18 05.58.32.401980 PM | 7 | COMPLETE | 1 | 4 | 862 | 29-SEP-18 01.30.09.602519 AM | 7 | COMPLETE | 7 | 5 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1082 | 07-NOV-18 04.25.22.587208 PM | 8 | COMPLETE | 8 | 4 | 993 | 23-OCT-18 09.12.34.064134 PM | 8 | COMPLETE | 1 | 5 | 430 | 26-JUN-18 07.51.51.514668 PM | 8 | COMPLETE | 1 | 6 | 240 | 08-MAY-18 12.27.09.346498 AM | 8 | COMPLETE | 1 | 7 | 196 | 28-APR-18 04.53.48.264575 PM | 8 | COMPLETE | 1 | 8 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 928 | 11-OCT-18 04.03.45.848273 AM | 9 | COMPLETE | 1 | 4 |
Rows 1 - 50. More rows exist. 0 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | -- | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ------ | - | | - | - | | ------ | - | | - | - | - | ---- | - | - | - | | -- | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - |
| 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 182 | 26-APR-18 03.56.02.680569 AM | 1 | COMPLETE | 1 | 4 | 159 | 20-APR-18 12.51.57.254596 PM | 1 | COMPLETE | 1 | 5 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 608 | 11-AUG-18 06.57.30.697703 PM | 3 | COMPLETE | 1 | 4 | 544 | 27-JUL-18 05.27.59.314441 AM | 3 | COMPLETE | 1 | 5 | 307 | 27-MAY-18 04.27.28.810727 AM | 3 | COMPLETE | 1 | 6 | 298 | 24-MAY-18 08.30.09.072186 PM | 3 | COMPLETE | 1 | 7 | 63 | 22-MAR-18 06.17.24.214468 AM | 3 | COMPLETE | 1 | 8 | 20 | 01-MAR-18 10.07.32.467335 AM | 3 | COMPLETE | 3 | 9 | 1 | 04-FEB-18 01.20.22.245677 PM | 3 | CANCELLED | 1 | 10 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 672 | 23-AUG-18 12.33.23.328945 PM | 4 | COMPLETE | 4 | 4 | 585 | 05-AUG-18 09.58.27.792726 PM | 4 | COMPLETE | 1 | 5 | 506 | 17-JUL-18 09.52.33.762499 PM | 4 | COMPLETE | 1 | 6 | 453 | 02-JUL-18 11.08.52.629924 PM | 4 | COMPLETE | 4 | 7 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 972 | 19-OCT-18 01.08.36.540797 PM | 6 | COMPLETE | 6 | 4 | 769 | 09-SEP-18 01.45.17.970634 PM | 6 | REFUNDED | 1 | 5 | 204 | 30-APR-18 11.39.15.779302 AM | 6 | COMPLETE | 1 | 6 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 950 | 15-OCT-18 05.58.32.401980 PM | 7 | COMPLETE | 1 | 4 | 862 | 29-SEP-18 01.30.09.602519 AM | 7 | COMPLETE | 7 | 5 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1082 | 07-NOV-18 04.25.22.587208 PM | 8 | COMPLETE | 8 | 4 | 993 | 23-OCT-18 09.12.34.064134 PM | 8 | COMPLETE | 1 | 5 | 430 | 26-JUN-18 07.51.51.514668 PM | 8 | COMPLETE | 1 | 6 | 240 | 08-MAY-18 12.27.09.346498 AM | 8 | COMPLETE | 1 | 7 | 196 | 28-APR-18 04.53.48.264575 PM | 8 | COMPLETE | 1 | 8 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 928 | 11-OCT-18 04.03.45.848273 AM | 9 | COMPLETE | 1 | 4 |
Rows 1 - 50. More rows exist.
1 - Statement 18
The subquery counts the number of orders each customer placed at each store. You can pass this to a SQL macro to return the top N customers for each store.
Get the three customers that placed the most orders at each store | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | -- | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ------ | - | | - | - | | ------ | - | | - | - | - | ---- | - | - | - | | -- | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - | | ---- | - | | - | - | | ---- | - | | - | - | - | ------ | - | | - | - | - | ------ | - | | - | - |
| 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 182 | 26-APR-18 03.56.02.680569 AM | 1 | COMPLETE | 1 | 4 | 159 | 20-APR-18 12.51.57.254596 PM | 1 | COMPLETE | 1 | 5 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 608 | 11-AUG-18 06.57.30.697703 PM | 3 | COMPLETE | 1 | 4 | 544 | 27-JUL-18 05.27.59.314441 AM | 3 | COMPLETE | 1 | 5 | 307 | 27-MAY-18 04.27.28.810727 AM | 3 | COMPLETE | 1 | 6 | 298 | 24-MAY-18 08.30.09.072186 PM | 3 | COMPLETE | 1 | 7 | 63 | 22-MAR-18 06.17.24.214468 AM | 3 | COMPLETE | 1 | 8 | 20 | 01-MAR-18 10.07.32.467335 AM | 3 | COMPLETE | 3 | 9 | 1 | 04-FEB-18 01.20.22.245677 PM | 3 | CANCELLED | 1 | 10 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 672 | 23-AUG-18 12.33.23.328945 PM | 4 | COMPLETE | 4 | 4 | 585 | 05-AUG-18 09.58.27.792726 PM | 4 | COMPLETE | 1 | 5 | 506 | 17-JUL-18 09.52.33.762499 PM | 4 | COMPLETE | 1 | 6 | 453 | 02-JUL-18 11.08.52.629924 PM | 4 | COMPLETE | 4 | 7 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 972 | 19-OCT-18 01.08.36.540797 PM | 6 | COMPLETE | 6 | 4 | 769 | 09-SEP-18 01.45.17.970634 PM | 6 | REFUNDED | 1 | 5 | 204 | 30-APR-18 11.39.15.779302 AM | 6 | COMPLETE | 1 | 6 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 950 | 15-OCT-18 05.58.32.401980 PM | 7 | COMPLETE | 1 | 4 | 862 | 29-SEP-18 01.30.09.602519 AM | 7 | COMPLETE | 7 | 5 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1082 | 07-NOV-18 04.25.22.587208 PM | 8 | COMPLETE | 8 | 4 | 993 | 23-OCT-18 09.12.34.064134 PM | 8 | COMPLETE | 1 | 5 | 430 | 26-JUN-18 07.51.51.514668 PM | 8 | COMPLETE | 1 | 6 | 240 | 08-MAY-18 12.27.09.346498 AM | 8 | COMPLETE | 1 | 7 | 196 | 28-APR-18 04.53.48.264575 PM | 8 | COMPLETE | 1 | 8 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 928 | 11-OCT-18 04.03.45.848273 AM | 9 | COMPLETE | 1 | 4 |
Rows 1 - 50. More rows exist. 2 | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | ---- | --- | ---- | | | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | | -- | ---- | - | -- | -- |
| 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
10 rows selected.
7 - Statement 19
This uses a named subquery to calculate the total paid for each line item, then filters to the top 3 per customer in the outer query. How to get top 10 rows in Oracle?select * from ( select * from the_table order by object_id ) where rownum <= 10; It's not pretty, but it is effective. In fact, it is very effective if the column (or columns) in the ORDER BY have a b-tree index. Oracle reads the index entries in order so that it can avoid having to sort the entire result set. How to get last 3 records in Oracle?SELECT * FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2 WHERE rownum <= 3 ORDER BY rownum DESC; Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution. How to get the maximum value of a row in Oracle?We used the OVER clause of the MAX function. The MAX function normally finds the max value in the entire table, but when we use the OVER clause and the PARTITION BY, we can group our MAX function. It's like saying, “get me the MAX value, but partition it by these values”. How do I limit the number of rows returned by an Oracle query after ordering?
The generic way of limiting the number of rows returned by an Oracle query after ordering is to use a subquery and ROWNUM. First, we order the data in the subquery and then use ROWNUM to limit the number of rows returned.
|
