sql - Is ORDER BY and ROW_NUMBER() deterministic? -
i've used sql in couple databases engines time time several years have little theoretical knowledge question "noobish" of you. become important me have ask.
imagine table urls non unique column status. , question assume have large amount of rows , status has same value in every record.
and imagine execute many times query:
select * urls order status do every time same row order or not? if happen if add new rows? change order or new records appended end of results? , if don't same order - on conditions depend order?
do
row_number() on (order status)return same order query above or based on different ordering mechanism?
it's simple. if want ordering can rely upon, need include enough columns in order by clause such combination of of columns unique each row. nothing else guaranteed.
for single table, can want listing columns "interesting" sort , including primary key column(s) afterwards. since pk, itself, guarantees uniqueness, whole combination guaranteed uniquely define ordering, e.g. if urls table has primary key of {site, page, ordinal} following give dependable result:
select * urls order status, site, page, ordinal
Comments
Post a Comment