How to get the first and last record from a SQL query?

Question

I have a table in PostgreSQL , I run a query on it with several conditions that return multiple rows ordered by one of the columns. In general, these are:

SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC 

Now I'm only interested in getting the first and last line from this query. I could get them outside of db, inside my application (and this is what I actually do), but it was interesting if for best performance I should not get from the database only those 2 records that really interest me.

And if so, how can I change my request?

Answer 1

[Caution: this may not be the most effective way to do this):

 (SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC LIMIT 1) UNION ALL (SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date ASC LIMIT 1) 

Answer 2

First recording:

 SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date ASC LIMIT 1 

Last record:

 SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC LIMIT 1 

Answer 3

You might want to try this, perhaps faster than two queries:

 select <some columns> from ( SELECT <some columns>, row_number() over (order by date desc) as rn, count(*) over () as total_count FROM mytable <maybe some joins here> WHERE <various conditions> ) t where rn = 1 or rn = total_count ORDER BY date DESC 

Answer 4

last record:

 SELECT * FROM `aboutus` order by id desc limit 1 

First recording:

 SELECT * FROM `aboutus` order by id asc limit 1 

Answer 5

 SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) UNION SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME) 

or

 SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) OR ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME) 

Answer 6

In all open methods, you still need to scan twice, one for the first line and one for the last line.

Using the window function "ROW_NUMBER () OVER (...)" plus "WITH Queries", you can scan only once and get both items.

Window Function: https://www.postgresql.org/docs/9.6/static/functions-window.html

With requests: https://www.postgresql.org/docs/9.6/static/queries-with.html

Example:

 WITH scan_plan AS ( SELECT <some columns>, ROW_NUMBER() OVER (ORDER BY date DESC) AS first_row, /*It logical required to be the same as major query*/ ROW_NUMBER() OVER (ORDER BY date ASC) AS last_row /*It rigth, needs to be the inverse*/ FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC) SELECT <some columns> FROM scan_plan WHERE scan_plan.first_row = 1 OR scan_plan.last_row = 1; 

Thus, you will do relationships, filtering and data processing only once.

Try using EXPLAIN ANALYZE in both directions.

Answer 7

 select * from {Table_Name} where {x_column_name}=( select d.{x_column_name} from ( select rownum as rno,{x_column_name} from {Table_Name})d where d.rno=( select count(*) from {Table_Name})); 

Answer 8

 SELECT MIN(Column), MAX(Column), UserId FROM Table_Name WHERE (Conditions) GROUP BY UserId DESC 

or

 SELECT MAX(Column) FROM TableName WHERE (Filter) UNION ALL SELECT MIN(Column) FROM TableName AS Tablename1 WHERE (Filter) ORDER BY Column 

Answer 9

 -- Create a function that always returns the first non-NULL item CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$ SELECT $1; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.FIRST ( sfunc = public.first_agg, basetype = anyelement, stype = anyelement ); -- Create a function that always returns the last non-NULL item CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$ SELECT $2; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.LAST ( sfunc = public.last_agg, basetype = anyelement, stype = anyelement ); 

Got it from here: https://wiki.postgresql.org/wiki/First/last_(aggregate)

Answer 10

why not use asc 1 limit order and desc 1 limit order desc 1 ..

?