Postgresql - sql. is there any way to write query without subquery

nc1318
March 7, 2024
198 views
1 vote
2 Answers

is there any way to write query without subquery?.

i have with:

with test as (
    select 1 as user_id, 'sadasdasggg1' as order_id, sysdate - 1 as order_date from dual
    union
    select 1, 'sadasdasggg2', sysdate - 2 from dual
    union
    select 1, 'sadasdasggg3', sysdate - 3 from dual
    union
    select 2, 'sadasdasggg4', sysdate - 4 from dual
)

i excepted to get ORDER_ID and the latest ORDER_DATE by user_id.

SELECT ORDER_ID, ORDER_DATE
FROM (
select s.*, row_number() over(PARTITION BY user_id ORDER BY order_date desc) rn
from test s
) WHERE rn = 1

and it works but i want to find out how to get that result without subquery

Answers

- MT0
- March 7, 2024 at 12:05 pm
- 0 votes
0
In Oracle:
```
SELECT MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY order_date) AS order_id,
       MAX(order_date) AS order_date
FROM   test
GROUP BY user_id
```
or, from Oracle 12:
```
SELECT order_id,
       order_date
FROM   test
ORDER BY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC)
FETCH FIRST ROW WITH TIES
```
Which, for your sample data, both output:

ORDER_ID ORDER_DATE

sadasdasggg1 2024-03-06 11:06:34

sadasdasggg4 2024-03-03 11:06:34

Oracle fiddle

The second option also works in PostgreSQL (from version 13) fiddle
Login or Signup to reply.

- Stefanovsm
- March 7, 2024 at 12:14 pm
- 0 votes
0
In PostgreSQL you can use values clause and distinct on.
```
with test(user_id, order_id, order_date) as (
 values
 (1,'sadasdasggg1',current_date-1),
 (1,'sadasdasggg2',current_date-2), 
 (1,'sadasdasggg3',current_date-3),
 (2,'sadasdasggg4',current_date-4)
)
select distinct on (user_id) order_id, order_date
from test
order by user_id, order_date desc;
```
DB Fiddle
Login or Signup to reply.