CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
zxc
zxc

Reputation: 1526

MYSQL returning the max value per group

I have a bit problem returning the max value of a group using mysql

this is my columns

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:23:00 | 1234
      1 | 2014/03/31     | 17:24:00 | 3467
      2 | 2014/03/31     | 17:26:00 | 2345

My query

SELECT id, max(date), MAX(time) , tran
FROM table
GROUP BY id

RESULT 

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:26:00 | 1234
      2 | 2014/03/31     | 17:24:00 | 2345

Expected answer should be 

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:26:00 | 3467
      2 | 2014/03/31     | 17:24:00 | 2345

Upvotes: 4

Views: 357

Answers (4)

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

Your Query:

SELECT id, max(date), MAX(time) , tran
FROM table
GROUP BY id

Now, you should use MAX(tran) and GROUP BY tran in your query to get the expected result.

Otherwise, you'll obviously end up with tran = 1234 since your query is only fetching those rows where date is MAX(date) and time is MAX(time) but tran value is normal tran value. Plus you are doing GROUP BY id whereas it must GROUP BY the maximum value of tran.

So, your query should be changed to something like this:

SELECT MAX(date), MAX(time), MAX(tran)
FROM table
GROUP BY tran

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can do this by using self join on the maxima from same table

SELECT t.* FROM
Table1 t
JOIN (
SELECT id, max(date) date, MAX(time) time , tran
FROM Table1
GROUP BY id) t2
ON(t.id=t2.id AND t.date=t2.date AND t.time=t2.time)

Fiddle

There may be differences between maxima of date and time so you should use a single field for saving date and time for you current schema this one is optimal

SELECT t.* FROM
Table1 t
JOIN (
SELECT id, 
  max(concat(date,' ',time)) `date_time`

FROM Table1
GROUP BY id) t2
ON(t.id=t2.id AND (concat(t.date,' ',t.time))=t2.date_time )

Fiddle

Upvotes: 5

Uriil
Uriil

Reputation: 12618

There is a great article on this theme which a read every time i am facing your problem. You might want to check it

Applying to you query, this will look like:

   SELECT *
   FROM `table`
   WHERE (
      SELECT count(*) FROM `table` AS t
      WHERE `t`.`id` = `table`.`id` AND `t`.`tran` <= `table`.`tran`
   ) < 2;

Best thing i like about this way, you can easily get top 2, 3 or any number of rows you need

Upvotes: 3

jawadxiv
jawadxiv

Reputation: 59

Have you tried the GREATEST() function:

SELECT GREATEST(field1, field2);

If you want to get the absolute maximum in mysql from all the rows, then you may want to use the following query:

SELECT GREATEST(MAX(field1), MAX(field2));

Please let me know if it helped

Upvotes: 0

Related Questions