1

i have simple table and need to group by duplicate items , also i need to get min with holding row i read this tutorial work , but not sum grouped item level http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

+------------+------+-------+----------+----+
| descendant | deep | level | ancestor | id |
+------------+------+-------+----------+----+
|          2 |    0 |     1 |        2 |  1 |
|          4 |    1 |     1 |        2 |  2 |
|          4 |    2 |     1 |       45 |  3 |
|          6 |    1 |     1 |     1368 |  4 |
|          6 |    2 |     1 |        6 |  5 |
|          6 |    3 |     1 |   123456 |  6 |
+------------+------+-------+----------+----+

i need group descendant and just show min deep row , bellow code work and not add level

SELECT  descendant,deep,ancestor, count(level)  FROM test as s1

         where deep = (SELECT min(s2.deep)
              FROM test s2
              WHERE s1.descendant = s2.descendant  )

 group by descendant

my Considered result

descendant | deep | ancestor |count(level)

2          |  0   |    2     |   1
4          |  1   |    2     |   2
6          |  1   |    1368  |   3

thanks

0

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

but with inner join we can holding row wit max or sum value

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.