CODEWITHSUNDEEP
mysql
ShoeLace1291
ShoeLace1291

Reputation: 4698

How to find the largest value of a column in mysql?

I am writing a forum application. I have a script that creates a board. In addition to the autoincremented board_id column, all boards have an integer column called position that is used to order the boards on the home page. When a new board is created, I want the default position to be the largest value within the rows of the boards table with the given category_id. Positions can have duplicates because they are positioned within their category. I hope that makes sense.

So if I have the following boards...

b_id | c_id | pos |
-------------------
  1  |   1  |  1  |
-------------------
  2  |   1  |  2  |
-------------------
  3  |   2  |  1  |
-------------------

And I am creating a new board in c_id 2, the position should be 2. If the new board is in c_id 1, the position would be 3. How can I do this?

The query below is what I am currently using, but the position always ends up being 0.

INSERT INTO `forum_boards` (
    `title`, 
    `description`, 
    `category_id`, 
    `position` 
) VALUES  (
    'Suggestion Box', 
     'Have an idea that will help us run things better? Let us know!',
     '1', 
    '(SELECT MAX(position), category_id FROM forum_boards WHERE category_id = 1)+1'
)

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can take the approach you are using. You need to drop the single quotes:

INSERT INTO `forum_boards` (`title`,  `description`, `category_id`, `position` 
                           )
    VALUES  ('Suggestion Box', 
             'Have an idea that will help us run things better? Let us know!',
             1, 
             (SELECT MAX(position) + 1 FROM forum_boards WHERE category_id = 1)'
             );

However, this doesn't take into account categories that are initially empty. And, I would write this using insert . . . select:

INSERT INTO `forum_boards` (`title`,  `description`, `category_id`, `position` 
                           )
    SELECT 'Suggestion Box', 
           'Have an idea that will help us run things better? Let us know!',
           1, 
           COALESCE(MAX(position) + 1, 1)
    FROM forum_boards
    WHERE category_id = 1;

Note that I dropped the single quotes around '1'. Numbers should be passed in as numbers, not strings.

Upvotes: 3

Related Questions