CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Florian
Florian

Reputation: 3171

How to get value from the first/last row in grouped rows?

I have this data structure for a table with trains:

 from | to | connection_id | date
------+----+---------------+------
  A   | B  | 1             | some
  B   | C  | 1             | dates
  B   | D  | 2             | I can
  D   | E  | 2             | sort
  E   | C  | 2             | by

And I want to group the data by the connection_id column, the result I want to have is:

 from | to | connection_id
------+----+---------------
  A   | C  | 1            
  B   | C  | 2

So I want to have the from-value from the first row in the grouped rows and the to-value from the last row from the grouped rows. To get the last and first rows, there is the date column to sort by. How do I get one value from the first/last row in grouped rows?

Edit: For Clarification: For each connection_id I want to have the from-value from the row with the lowest date-value and the to-value from the row with the highest date-value.

Upvotes: 0

Views: 199

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

You could try order by on two select with union. Like the following:

Select * from yourtable 
group by connection_id
order by [from] desc limit 1
union
select * from yourtable
group by connection_id 
order by [to] asc limit 1

Upvotes: 1

fthiella
fthiella

Reputation: 49049

If rows can be ordered by date field, I think you could use this:

select t1.`from`, t2.`to`, mm.connection_id
from (
  select connection_id, min(`date`) as minDate, max(`date`) as maxDate
  from trains
  group by connection_id) mm
  inner join trains t1 on mm.connection_id=t1.connection_id and t1.`date`=mm.minDate
  inner join trains t2 on mm.connection_id=t2.connection_id and t2.`date`=mm.maxDate

Here I am selecting the first and the last row of every connection_id, taking the from column from the first row, and the to column from the last. Or you could also use this trick:

select
  SUBSTRING_INDEX(GROUP_CONCAT(`from` ORDER BY `date`), ',', 1 ) as `from`,
  SUBSTRING_INDEX(GROUP_CONCAT(`to` ORDER BY `date`), ',', -1 ) as `to`,
  connection_id
from trains
group by connection_id

Upvotes: 3

Related Questions