select only one record from a query which returns several rows-CodePudding

How do I retrieve only one row from a query which returns several? Let's say I want only the 3 row?

This is the query but I want only the 3rd result

SELECT (journeys.id, j_starting_channel)
AS JER FROM JOURNEYS
WHERE j_starting_channel = 'channel_name' ORDER BY journeys.id;

CodePudding user response：

The following should get you there:

SELECT (journeys.id, j_starting_channel)
AS JER FROM JOURNEYS
WHERE j_starting_channel = 'channel_name' ORDER BY journeys.id
LIMIT 1
OFFSET 2

LIMIT n will return the first n results. OFFSET m skips the first m rows and only returns everything thereafter. LIMIT n OFFSET m thus returns rows m 1 to m n.

See the PostgreSQL documentation for more details: https://www.postgresql.org/docs/9.5/sql-select.html

CodePudding user response：

If you just need to skip some rows then you can just use OFFSET to skip the rows in the top and then use LIMIT to return just one row

Like this:

SELECT (journeys.id, j_starting_channel)
AS JER FROM JOURNEYS
WHERE j_starting_channel = 'channel_name' ORDER BY journeys.id LIMIT 1 OFFSET 2

Here you have a step-by-step tutorial on those clauses

https://www.postgresqltutorial.com/postgresql-limit/

And you can always refer to the documentation too

CodePudding user response：

by using OFFSET, LIMIT you can get needed portion of rows from result set

SELECT (journeys.id, j_starting_channel)
AS JER FROM JOURNEYS
WHERE j_starting_channel = 'channel_name' ORDER BY journeys.id OFFSET 2 LIMIT 1;