PostgreSQL drop all the even rows and output all the odd rows-CodePudding

I have a table, and I want to ignore all the even rows and output all the odd rows in PostgreSQL. The table is like:

col1	col2
Cell 1	Cell 2
Cell 3	Cell 4
Cell 5	Cell 6
Cell 7	Cell 8

The code to create the sample table is as following:

CREATE TABLE tmp (
  col1 varchar(255) DEFAULT NULL,
  col2 varchar(255) DEFAULT NULL
);
INSERT INTO tmp VALUES ('Cell 1', 'Cell 2');
INSERT INTO tmp VALUES ('Cell 3', 'Cell 4');
INSERT INTO tmp VALUES ('Cell 5', 'Cell 6');
INSERT INTO tmp VALUES ('Cell 7', 'Cell 8');

And that is what I expect:

col1	col2
Cell 1	Cell 2
Cell 5	Cell 6

I know row_number() over () can get the row number, but based on my understanding, it only works in select part, and I'm not sure if it works for where part.

It would be better if it can be solved by only ONE query.

Apppreciate any ideas! Thank you!

CodePudding user response：

We can use ROW_NUMBER here. Assuming there be a third id column which maintains the sequence order, we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM tmp
)

SELECT col1, col2
FROM cte
WHERE rn % 2 = 1;