Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
distinct on vs row_number
drop table if exists houses; create table houses as select x as hss_id, (random()*100)::int as hss_num, (random()*10)::int as hss_street, (random()*1000000)::int as hss_price, (now() - make_interval(days := (random()*1000)::int))::date as hss_saled from generate_series(1,100000) as x; create index idx_hss_street_num_saled on houses(hss_street, hss_num, hss_saled desc); analyze houses; explain(analyse, verbose) select * from ( select houses.*, row_number() over(partition by hss_street, hss_num order by hss_saled desc) as rn from houses ) tt where rn = 1; explain(analyse, verbose) select distinct on(hss_street, hss_num) * from houses order by hss_street, hss_num, hss_saled desc;
run
|
edit
|
history
|
help
0
Projeto
Timestamps in PostgreSQL
Query workaround in order to avoid IN Operator in WHERE Clause
Assignment 1(SET A)
LeetCode 262
loggin_trigger-audit
postgres group by by function
book suggestion
SQL2_CLASS
cte