CODEWITHSUNDEEP
mysqlmariadb
Srijani Ghosh
Srijani Ghosh

Reputation: 4216

How to find out the row with max Number in a column?

I have a sql query which goes like this-

select * from (SELECT parent.item_name as name, parent.lft as lft
FROM item_tree AS item,
    item_tree AS parent
WHERE item.lft > parent.lft AND item.rgt < parent.rgt
    AND item.item_id = 6) 
      as ABC

This returns a table - 

name   |   lft

A      |     1
B      |     2
C      |     3

But, I need the value C as it has the max lft value. So, I modified the code as below - 

select * from (SELECT parent.item_name as name, parent.lft as lft
FROM item_tree AS item,
    item_tree AS parent
WHERE item.lft > parent.lft AND item.rgt < parent.rgt
    AND item.item_id = 6) 
      as ABC
    having ABC.lft = MAX(ABC.lft)

Now, this query returns no value. What is the problem in this code?

Thanks in advance!

Upvotes: 0

Views: 47

Answers (2)

Mattia Caputo
Mattia Caputo

Reputation: 969

Try this query 

    SELECT name, lft
    from (SELECT parent.item_name as name, parent.lft as lft
    FROM item_tree AS item,
        item_tree AS parent
    WHERE item.lft > parent.lft AND item.rgt < parent.rgt
        AND item.item_id = 6) 
          as ABC
    ORDER BY lft DESC
    LIMIT 1

Upvotes: 1

Artemiy StagnantIce Alexeew
Artemiy StagnantIce Alexeew

Reputation: 862

MAX is agregate function, and not working without GROUP BY.

Try this:

select * from (SELECT parent.item_name as name, parent.lft as lft
FROM item_tree AS item,
    item_tree AS parent
WHERE item.lft > parent.lft AND item.rgt < parent.rgt
    AND item.item_id = 6) 
      as ABC
    order by ABC.lft desc limit 1

Upvotes: 0

Related Questions