CODEWITHSUNDEEP
mysqlstored-procedures
Dnakk Jam
Dnakk Jam

Reputation: 371

How to get last value in multiple rows in mysql?

How to select last value in multiple rows in MySQL ?

select name,date,value from table1;

Name    Date               Value
A       01-Jan-2013         3
A       02-Jan-2013         4
B       04-Jan-2013         2
B       05-Jan-2013         8

Result I need :

Name    Date               Value
A       01-Jan-2013         3
A       02-Jan-2013         4
        Last_value          4
B       04-Jan-2013         2
B       05-Jan-2013         8
        Last_value          8

How to do that in MySQL store procedure ?

Regards

Upvotes: 2

Views: 1751

Answers (3)

sgeddes
sgeddes

Reputation: 62831

This seems better suited for your Presentation Layer, but it is possible to write it using SQL. Something like this should work using UNION ALL to return that last record per group:

SELECT Name, Dt, Value
FROM (
    SELECT Name, Dt, Value, Name Name2
    FROM YourTable 
    UNION ALL
    SELECT '' Name, 'Last_Value', T.Value, T.Name Name2
    FROM YourTable T
       JOIN (
         SELECT Name, MAX(dt) MaxDt
         FROM YourTable
         GROUP BY Name
       ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
    ORDER BY Name2, Dt, Value
  ) t

SQL Fiddle Demo

Resulting in:

NAME         DT                      VALUE
A            2013-01-01 00:00:00     3
A            2013-01-02 00:00:00     4
             Last_Value              4
B            2013-01-04 00:00:00     2
B            2013-01-05 00:00:00     8
             Last_Value              8

EDIT: To get the Summed Group Total, you have to introduce the use of user defined variables. Here is an example:

SELECT Name, Dt, IF(Name='',summedTotal,Value) Value
FROM (
  SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
    Name, Dt, Value, Name Name2,
    @prevRow:=Name
  FROM YourTable
    JOIN (SELECT @summedTotal:=0) t
  UNION ALL
  SELECT summedTotal, '' Name, 'Last_Value', T.Value, T.Name Name2, pr
  FROM (
      SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
        Name, Dt, Value, Name Name2,
        @prevRow:=Name pr
      FROM YourTable
        JOIN (SELECT @summedTotal:=0) t
    ) T
    JOIN (
      SELECT Name, MAX(dt) MaxDt
      FROM YourTable
      GROUP BY Name
    ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
  ORDER BY Name2, Dt, Value
  ) t

And more SQL Fiddle

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can select the MAX value using inner join. But you haven't specified your primary key column.

SELECT
  m.*
FROM mytable AS m
  INNER JOIN (SELECT
        MAX(primary_key_column)
          FROM mytable
          GROUP BY Name) AS l
    ON l.primary_key_column = m.primary_key_column

Upvotes: 0

Brad Koch
Brad Koch

Reputation: 20267

There's no "last" record. You need to sort them by the column they should be ordered by, then only select the last row.

/* SELECT ... */ ORDER BY sort_column DESC LIMIT 1

Upvotes: 1

Related Questions