How to Get First and Last Record from a SQL Query

How to get First and Last record from a sql query?

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

How to get the first and the last record per group in SQL Server 2008?

How about using ROW_NUMBER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RnAsc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val),
        RnDesc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val DESC)
    FROM tbl
)
SELECT
    id, [group], val, start, [end]
FROM Cte
WHERE
    RnAsc = 1 OR RnDesc = 1
ORDER BY [group], val

Selecting First and Last record from a table in SQL Server

Use MAX and MIN:

SELECT 
  MAX(titlenumber) AS MaxTitleNumber, 
  MIN(titlenumber) AS MinTitleNumber
FROM cataloguebase

SQL query - selecting first and last record of each day in a month

The tricky part of this requirement is the double rows for the dates that have only 1 registration, this is why I use UNION ALL.

Aggregation is needed to get the first and last startDateTime of each day and finally joins:

select r.registrationID, p.timeNeeded
from (
  select registrationID, projectID, startDateTime 
  from Registration
  union all
  select max(registrationID), max(projectID), max(startDateTime) 
  from Registration
  group by date(startDateTime)
  having count(*) = 1
) r
inner join ( 
  select date(startDateTime) date, 
         min(startDateTime) min_date,
         max(startDateTime) max_date
  from Registration
  where date_format(startDateTime, "%Y-%m") = date_format(current_date, "%Y-%m")
  group by date
) t on r.startDateTime in (t.min_date, t.max_date) 
inner join Project p on p.projectID = r.projectID
order by r.startDateTime

See the demo.

Results:

| registrationID | timeNeeded |
| -------------- | ---------- |
| reg1           | 50         |
| reg2           | 50         |
| reg3           | 30         |
| reg6           | 20         |
| reg7           | 30         |
| reg7           | 30         |
| reg8           | 50         |
| reg8           | 50         |
| reg9           | 30         |
| reg10          | 20         |

How to get first and last record from same group in SQL Server?

You have a sequence number so you can use some form of aggregation. One method is:

select records,
       max(case when sequence = 1 then fromregion end) as fromregion,
       max(case when sequence = maxsequence then toregion) as toregion
from (select t.*, max(sequence) over (partition by records) as max_sequence
      from t
     ) t
group by records;

Unfortunately, SQL Server doesn't offer "first()" or "last()" as aggregation functions. But it does support first_value() as a window function. This allows you to do the logic without a subquery:

select distinct records,
       first_value(fromRegion) over (partition by records order by sequence) as fromregion,
       first_value(toRegion) over (partition by records order by sequence desc) as toregion
from t;

SQL - Keep only the first and last record of each day

this will improve performance when searching on dates.

ALTER TABLE chronicle
ADD INDEX `ix_chronicle_time` (`time` ASC);

This will delete the records:

CREATE TEMPORARY TABLE #tmp_ids (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO #tmp_ids (id)
SELECT
    min(id)
FROM
    chronicle
GROUP BY
    CAST(day as DATE),
    data1,
    data2
UNION
SELECT
    Max(id)
FROM
    chronicle
GROUP BY
    CAST(day as DATE),
    data1,
    data2;

DELETE FROM
    chronicle
WHERE
    ID not in (select id FROM #tmp_ids)
    AND date <= '2015-01-01'; -- if you want to consider all dates, then remove this condition

How to get first and last record of each user in MySQL (for attendance)

I've suggest with this single query to cross check with the expected result:

SELECT 
   GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' ') all_id, 
   -- this return all id present in the group
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',1) min_id_in,
   -- this part is taking the first value from the GROUP_CONCAT operation above
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',-1) max_id_in,
   -- this part is taking the last value from the first GROUP_CONCAT operation
   user_id,room_id,
   MIN(created), 
   MAX(created) -- Min/max value are both shown in same query
FROM access_log 
GROUP BY user_id,room_id,
date(created); -- the missing condition where OP's asks results to return by each date.

I've added date(created) in the GROUP BY .. condition.

In your original query from the fiddle:

SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- I've added the condition here 
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;

SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- and here
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;

select first and last record of each group horizontally

I think this is much simpler using window functions and select distinct:

select distinct facility_id, name,
       first_value(value) over (partition by facility_id, name order by created_at asc) as first_value,
       min(created_at) as first_created_at,
       first_value(value) over (partition by facility_id, name order by created_at desc) as last_value,
       max(created_at) as last_created_at
from t;

No subqueries. No joins.

You can also use arrays to accomplish the same functionality, using group by. It is a shame that SQL Server doesn't directly support first_value() as a window function.

Get the first and last record of each item for the month

You are getting the same quantities because you are aggregating by quantity in the group by as well as product. Your version of the query, properly written would be:

SELECT ProductID, MIN(Quantity) AS Starting, MAX(Quantity) AS Ending 
FROM Records
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

However, this doesn't give you the first and last values. It only gives you the minimum and maximum ones. To get those values, use row_number() and conditional aggregation:

SELECT ProductID,
       MAX(CASE WHEN seqnum_asc = 1 THEN Quantity END) as Starting,
       MAX(CASE WHEN seqnum_desc = 1 THEN Quantity END) as Ending
FROM (SELECT r.*,
             row_number() over (partition by product order by dateadded asc) as seqnum_asc,
             row_number() over (partition by product order by dateadded desc) as seqnum_desc
      FROM Records r
     ) r
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

If you are using SQL Server 2012, then you can also use this with FIRST_VALUE() and LAST_VALUE() instead of row_number().

---

Related Topics