MySQL 如何从mysql表中找到第一条和最后一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2735395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to find first and last record from mysql table
提问by chetan
I have one table I want to find first and last record that satisfy criteria of particular month.
我有一张表，我想找到满足特定月份条件的第一条和最后一条记录。
回答by codaddict
First and last make sense only when you have the output of the query sorted on a field(s).
只有当您将查询的输出按字段排序时，第一个和最后一个才有意义。
To get the first record:
获取第一条记录：
select col1 from tab1 order by col1 asc limit 1;
To get the last record:
获取最后一条记录：
select col1 from tab1 order by col1 desc limit 1;
回答by bdereta
SELECT
(SELECT column FROM table WHERE [condition] ORDER BY column LIMIT 1) as 'first',
(SELECT column FROM table WHERE [condition] ORDER BY column DESC LIMIT 1) as 'last'
This worked for me when I needed to select first and the last date in the event series.
当我需要选择事件系列中的第一个和最后一个日期时，这对我有用。
回答by paxdiablo
How about something like:
怎么样：
select 'first', f1, f2, f3, f4 from tbl
order by f1 asc, f2 asc
limit 1
union all
select 'last', f1, f2, f3, f4 from tbl
order by f1 desc, f2 desc
limit 1
Obviously feel free to add whatever condition you want in a whereclause but the basic premise of the order byis to reverse the order in the two selectsections.
显然可以随意在where子句中添加任何您想要的条件，但 的基本前提order by是颠倒两个select部分的顺序。
The limitclause will just get the first row in both cases. That just happens to be the lastrow of set in the second selectdue to the fact that you've reversed the ordering.
limit在这两种情况下，该子句都将获得第一行。由于您颠倒了顺序，这恰好是第二个集合的最后一行select。
If there is only one row resulting from your conditions and you don't want it returned twice, use unioninstead of union all.
如果您的条件只产生一行并且您不希望它返回两次，请使用union代替union all。
回答by Salil
select * from table
where id = (select id from tab1 order by col1 asc limit 1) or
id = (select id from tab1 order by col1 desc limit 1);
回答by Nikita Jain
SELECT * FROM (
SELECT first_name, LENGTH(first_name) FROM Employees
ORDER BY LENGTH(first_name) ASC
FETCH FIRST 1 rows ONLY)
UNION
SELECT * FROM (
SELECT first_name, LENGTH(first_name) FROM Employees
ORDER BY LENGTH(first_name) DESC
FETCH FIRST 1 rows ONLY)
ORDER BY 2 desc;
