如何从 sql 查询获取第一条和最后一条记录？

[Caveat: Might not be the most efficient way to do it]:

(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)

First record:

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

SELECT <rows> FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME)
UNION
SELECT <rows> 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)

You might want to try this, could potentially be faster than doing 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

last record :

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

first record :

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

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}));

Why not use order by asc limit 1 and the reverse, order by desc limit 1?

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

-- 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)

In all the exposed ways of do until now, must go through scan two times, one for the first row and one for the last row.

Using the Window Function "ROW_NUMBER() OVER (...)" plus "WITH Queries", you can scan only one time and get both items.

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

WITH Queries: 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's logical required to be the same as major query*/
ROW_NUMBER() OVER (ORDER BY date ASC) AS last_row /*It's 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;

On that way you will do relations, filtrations and data manipulation only one time.

Try some EXPLAIN ANALYZE on both ways.

How to get the First and Last Record of DB in c#.

SELECT TOP 1 *
FROM ViewAttendenceReport
WHERE EmployeeId = 4
AND AttendenceDate >='1/18/2020 00:00:00'
AND AttendenceDate <='1/18/2020 23:59:59'
ORDER BY Intime ASC
UNION
SELECT TOP 1 *
FROM ViewAttendenceReport
WHERE EmployeeId = 4
AND AttendenceDate >='1/18/2020 00:00:00'
AND AttendenceDate <='1/18/2020 23:59:59'
ORDER BY OutTime DESC;

I think this code gets the same and is easier to read.

SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE date >= (SELECT date from mytable)
OR date <= (SELECT date from mytable);

In some cases useful the WINDOW functions FIRST_VALUE() and LAST_VALUE(). The key benefit - this query is readeable, sort data only once and it is only one query for several columns.

 SELECT
FIRST_VALUE(timestamp) over w as created_dt,
LAST_VALUE(timestamp) over w as last_update_dt,
LAST_VALUE(action) over w as last_action
FROM events
WINDOW w as (ORDER BY timestamp ASC)

It can be used for getting fisrt and last rows by some ID

SELECT DISTINCT
order_id,
FIRST_VALUE(timestamp) over w as created_dt,
LAST_VALUE(timestamp) over w as last_update_dt,
LAST_VALUE(action) over w as last_action
    

FROM events as x
WINDOW w as (PARTITION BY order_id ORDER BY timestamp ASC)

I know this is a 7 year old thread, but the question was nearly identical and the accepted answer was what I started this with and eventually optimized to the following, which in my case returns consistently 85ms +-5ms with <some_column> being an indexed int field.

note1: the UNION ALL example in the accepted answer works too but was less performant in my case coming in at 300ms +-20ms.

note2: the next most upvoted answer (the row counter example) also works but was the least performant in my case coming in at 800ms +-70ms.

select
(select <some_column> from <some_table>
order by <some_field> limit 1)        as oldest,
(select <some_column> from <some_table>
order by <some_field> desc limit 1)   as newest
;

I did note that op referenced possible joins. I haven't had the need to include joins for my own purposes (just getting the current low and high IDs in fairly dynamic view) but with this model, the subqueries for oldest and newest should be able to be full fledged queries. Haven't tested, so not sure if it would work or be optimal.

I did test this model (which may also have already been suggested above) which might be a bit easier to join against, but the performance as-is was just a bit less than half of the example above, consistently returning 220ms +-10ms in my case.

select oldest.<some_field> as old,
newest.<some_field> as new
from
(select <some_column> from <some_table>
order by <some_field> limit 1)        as oldest,
(select <some_column> from <some_table>
order by <some_field> desc limit 1)   as newest
;

The correct Sql listed below

SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;


SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MAX(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;