Deterministic Sort Order For Window Functions

I've a status table and I want to fetch the latest details. Slno | ID | Status | date 1 | 1 | Pass | 15-06-2015 11:11:00 - this is inserted first 2 | 1 | Fail | 1

Solution 1:

If you don't have slno in your table, then you don't have any reliable information which row was inserted first. There is no natural order in a table, the physical order of rows can change any time (with any update, or with VACUUM, etc.)

You could use an unreliable trick: order by the internal ctid.

select*from  (
   select id, status
        , row_number() OVER (PARTITIONBY id
                             ORDERBYdate, ctid) AS row_num
   from   status  -- that's your table name??wheredate>='2015-06-01'-- assuming column is actually a dateanddate<'2015-07-01'
  ) sub
where  row_num =1;

• In absence of any other information which row came first (which is a design error to begin with, fix it!), you might try to save what you can using the internal tuple ID ctid

  • In-order sequence generation

  Rows will be in physical order when inserted initially, but that can change any time with any write operation to the table or VACUUM or other events. This is a measure of last resort and it will break.

• Your presented query was invalid on several counts: missing column name in 1st CTE, missing table name in 2nd CTE, ...

• You don't need a CTE for this.

Simpler with DISTINCT ON (considerations for ctid apply the same):

SELECTDISTINCTON (id)
       id, status
FROM   status
WHEREdate >= '2015-06-01'ANDdate <  '2015-07-01'ORDERBY id, date, ctid;

• Select first row in each GROUP BY group?