in Postgres 9.1 is it possible to skip row(s) if the value of NAME is equal to the one before f.e. following table
ID | NAME | AGE | SEX | CLASS
---------------------------------
1 Paul 17 M 2b
2 Paul 16 M 2b
3 Paul 18 F 2b
4 Lexi 18 M 2b
5 Sarah 16 F 2b
6 Sarah 17 F 2b
The result should be:
1 Paul 17 M 2b
4 Lexi 18 M 2b
5 Sarah 16 F 2b
Thanks for your help,
t book
select *
from (
select id,
name,
age,
sex,
class,
lag(name) over (order by id) as prev_name
from the_table
) as t
where name <> prev_name;
alternatively
select *
from (
select id,
name,
age,
sex,
class,
row_number() over (partition by name order by id) as rn
from the_table
) as t
where rn = 1;
Another option would be to use Postgres' distinct on operator:
select distinct on (name)
id,
name,
age,
sex,
class
from the_table
order by name,id
but that will return the result ordered by name (which is limitation of the distinct on operator). If you don't want that you'll need to wrap this again:
select *
from (
select distinct on (name)
id,
name,
age,
sex,
class
from the_table
order by name,id
) t
order by id;
SELECT ID , NAME , AGE , SEX , CLASS
FROM thetable t
WHERE NOT EXISTS (
SELECT * FROM thetable nx
WHERE nx.NAME = t.NAME
-- AND nx.ID < t.ID -- ANY one before it
AND nx.ID = t.ID-1 -- THE one before it
);
UNIQUE ON. For queries that outgrow work_mem, EXIST will probably win again.