Thread: querying the value of the previous row

In response to Chris Velevitch :
> I'm to write a query like:-
>
> select
>      case when column_name1 <> value_of_previous(column_name1)
>           then column_name1 end as column
>     ,column_name2
> from table
> ordered by column_name1, column_name2

Okay, with this table:

test=# select * from foo;
 col1 | col2
------+------
    1 |    1
    1 |    2
    1 |    3
    2 |    4
    2 |    5
    2 |    6
    2 |    7
    3 |    8
    4 |    9
    5 |   10
(10 rows)

you can do:

test=# select
  case when col1::text <> coalesce(lag::text,'NULL') then col1 else null end as col1,
  col2
from (
  select col1, lag(col1) over (range unbounded preceding ),
         col2
  from foo
  order by col2
) foo order by col2;
 col1 | col2
------+------
    1 |    1
      |    2
      |    3
    2 |    4
      |    5
      |    6
      |    7
    3 |    8
    4 |    9
    5 |   10
(10 rows)



>
> How do I do this? (I'm using pg 7.4)

Unfortunately (for you), i'm using a window-function, in this case
lag(), new since 8.4.  Your version 7.4 has reached End-of-Lifetime, so
i suggest you update to 8.4.



Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Chris Velevitch wrote:
> I'm to write a query like:-
>
> select
>      case when column_name1 <> value_of_previous(column_name1)
>           then column_name1 end as column
>     ,column_name2
> from table
> ordered by column_name1, column_name2
>
> in order to get:-
>
> column        | column_name2
> --------------+--------------
> value_1_c1    |   value_1_c2
>               |   value_2_c2
>               |   value_3_c2
> value_2_c1    |   value_4_c2
>               |   value_5_c2
>               |   value_6_c2
> value_3_c1    |   value_7_c2
>               |   value_8_c2
>               |   value_9_c2
>
> How do I do this? (I'm using pg 7.4)

what does 'previous' mean here?   thats not a concept SQL really has.

anyways, that sort of report output is something you usually do via your
reporting code