how to find first and last record from mysql table

I have one table I want to find first and last record that satisfy开发者_运维百科 criteria of particular month.

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.

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;

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 where clause but the basic premise of the order by is to reverse the order in the two select sections.

The limit clause will just get the first row in both cases. That just happens to be the last row of set in the second select due to the fact that you've reversed the ordering.

If there is only one row resulting from your conditions and you don't want it returned twice, use union instead of union all.

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

Try this one: take for example you want to group your table based on group_col and get the first and last value of value_col:

select substring_index(group_concat(value_col), ',',1) as 'first',
        substring_index(group_concat(value_col), ',',-1) as 'last' 
from table
group by group_col

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;