"""Authored latest-row-per-group ties, SQLite memory; not validity/business freshness."""
import sqlite3,json
def demo():
    rows=[(1,'A',10,'old-A'),(2,'A',20,'candidate-A2'),(3,'A',20,'candidate-A3'),(4,'B',5,'old-B'),(5,'B',8,'candidate-B5'),(6,'B',8,'candidate-B6')]
    c=sqlite3.connect(':memory:');c.execute('CREATE TABLE history(id INTEGER PRIMARY KEY,entity TEXT NOT NULL,t INTEGER NOT NULL,payload TEXT NOT NULL)');c.executemany('INSERT INTO history VALUES(?,?,?,?)',rows)
    bare=[list(x)for x in c.execute('SELECT entity,id,payload,MAX(t) FROM history GROUP BY entity ORDER BY entity')]
    prefix='WITH ranked AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY entity ORDER BY t DESC,id DESC) AS rn FROM history) SELECT id,entity,t,payload FROM ranked WHERE rn=1 '
    selected=[list(x)for x in c.execute(prefix+'ORDER BY entity ASC')];reversed_output=[list(x)for x in c.execute(prefix+'ORDER BY entity DESC')]
    oracle=[list(max((x for x in rows if x[1]==e),key=lambda x:(x[2],x[0])))for e in ['A','B']]
    assert selected==oracle and [x[0]for x in selected]==[3,6]and [x[0]for x in reversed_output]==[6,3]
    for e,id,payload,t in bare:assert id in ({2,3}if e=='A'else{5,6})and t==(20 if e=='A'else 8)
    count=c.execute('SELECT COUNT(*) FROM history').fetchone()[0];assert count==6;c.close()
    return {'sqlite_version':sqlite3.sqlite_version,'authored_history':[list(x)for x in rows],'bare_columns_single_builtin_max_observed':bare,'deterministic_query':prefix+'ORDER BY entity ASC','selected':selected,'python_tie_policy_oracle':oracle,'same_window_different_final_order':reversed_output,'history_rows_retained':count,'scope':'t is authored non-NULL integer ordering key; max id at tied t is declared selection policy, not proof of later real event. SQLite single built-in MIN/MAX bare-column exception acknowledged; tied row selection not guaranteed. No RAG validity filter, server DB or chronology truth verified.'}
if __name__=='__main__':print(json.dumps(demo(),indent=2))