Comparing row values 2

[blade7]

This is probably old hat to y'all but I'm relatively new to Postgres. I need to flag records where a row is not greater than the previous (records are in sequential order). See the example below.

Col1 Seq Col3 Flag
A 1 35
B 2 36
C 3 32 1
D 4 39
E 5 40

No problems with finding the gaps in the sequencing but this one is stumping me and I couldn't find an existing forum thread(s) related to this. TIA

 

[ceco]

It is not a problem that can be solved with clean SQL

you should use plpgsql function, or do it with client application

 

[feherke]

Hi

Why not this ?

[blue]mydb #[/blue] create table blade7 ( col1 varchar(1), seq integer, col3 integer,flag smallint);
CREATE TABLE

[blue]mydb #[/blue] copy blade7 (col1,seq,col3) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
[blue]>>[/blue] A    1       35
[blue]>>[/blue] B    2       36
[blue]>>[/blue] C    3       32
[blue]>>[/blue] D    4       39
[blue]>>[/blue] E    5       40
[blue]>>[/blue] \.

[blue]mydb #[/blue] [b]update blade7 set flag=1 from blade7 prev where prev.seq=blade7.seq-1 and blade7.col3<prev.col3;[/b]
UPDATE 1

[blue]mydb #[/blue] select * from blade7 order by seq;
 col1 | seq | col3 | flag 
------+-----+------+------
 A    |   1 |   35 |     
 B    |   2 |   36 |     
 C    |   3 |   32 |    1
 D    |   4 |   39 |     
 E    |   5 |   40 |     
(5 rows)

Feherke.

http://rootshell.be/~feherke/

 

[blade7]

Thank you. Did the trick!