Create one row from multiple rows in SQL

Question

I'm want to create one row that includes all the latest data from my table, I found the way to do it separately for each column, is it possible to do it in one query?

This will be the wanted answer table:

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) Same problem, you're just looking for "last" rather than first. — Uueerdo, Aug 03 '18 at 18:40
@Uueerdo Since that is for Postgres I don't think that's the right duplicate link. It's definitely a duplicate though, but this one is closer https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — Error_2646, Aug 03 '18 at 18:47
Possible duplicate of [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — BenRoob, Aug 03 '18 at 18:48
A more helpful "duplicate" SO-post is probably this one https://stackoverflow.com/questions/8748986/get-records-with-highest-smallest-whatever-per-group/8749095#8749095 as it refers to MySQL. — Carsten Massmann, Aug 03 '18 at 18:52
@Error_2646 that answer has a vendor-agnostic version as well. — Uueerdo, Aug 03 '18 at 18:52

score 1 · Answer 1 · answered Aug 03 '18 at 18:35

1

You can use correlated subquery :

select t.*
from table t
where time1 = (select max(t1.time1) from table t1 where t1.p_id = t.p_id);

answered Aug 03 '18 at 18:35

Yogesh Sharma

49,870
5
26
52

in this case my ccc column will be empty because there is no data for ccc where max time1 for p_id = 1 – m.haz Aug 03 '18 at 18:52

ScaisEdge · Answer 2 · 2018-08-03T19:05:26.967

1

if you need the rows related to max_daye you could use a join with the subquery for max time1

select   m.p_id, m.aaa, m.bbb, m.ccc 
from mytable m
inner join (
  SELECT p_id,  max(time1) max_date 
  FROM   mytable
  group by p_id 
) t on t.max_date =  m.time1  and t.p_id = m.p_id

or if you need only the max vale for each p_id you can use

select   m.p_id, max(m.aaa), max(m.bbb), max(m.ccc )
from mytable m
group by m-p_id

edited Aug 03 '18 at 19:05

answered Aug 03 '18 at 18:49

ScaisEdge

131,976
10
91
107

the problem is that not all the columns are full, in this case for p_id 1 the ccc column will be empty – m.haz Aug 03 '18 at 19:00
then you need only the max value for each p_id ... answer updated .. hope is useful – ScaisEdge Aug 03 '18 at 19:05

score 0 · Answer 3 · answered Aug 03 '18 at 18:59

You could use correlated subqueries fetching the "latest" non null values for each column for each distinct ID.

SELECT t1.p_id,
       (SELECT t2.aaa
               FROM elbat t2
               WHERE t2.p_id = t1.p_id
                     AND t2.aaa IS NOT NULL
               ORDER BY t2.time1 DESC
               LIMIT 1) aaa,
       ...
       (SELECT t2.ccc
               FROM elbat t2
               WHERE t2.p_id = t1.p_id
                     AND t2.ccc IS NOT NULL
               ORDER BY t2.time1 DESC
               LIMIT 1) ccc,
       FROM (SELECT DISTINCT
                    p_id
                    FROM elbat) t1;

Create one row from multiple rows in SQL

3 Answers3