CODEWITHSUNDEEP
mysqldatabaseselectinsert
user926750
user926750

Reputation:

MAX in SELECT statement

username   level
me          1
you         1
me          2
me          3
you         1

I want to get the highest value for each username. So it should give me 3 for 'me', and 1 for 'you'. How do I do this in a SELECT statement?

INSERT INTO table1 (username, level)
SELECT username, (MAX(level)
WHERE username=username group by username)
FROM table2

^ It should be something like that but I can't figure it out.

--EDIT-- This is giving me the same value for the entire level and exp column while they're definitely not all the same:

INSERT INTO oitc_test (username, xpboost_expiration, level, exp, kills, games) 
SELECT oitc.username, oitc.xpboost_expiration, MAX(oitc.level), MAX(oitc.exp), oitc_sum.kills, oitc_sum.games 
FROM oitc, oitc_sum 
GROUP by username;

What am I doing wrong?

Upvotes: 1

Views: 72

Answers (1)

hjpotter92
hjpotter92

Reputation: 80639

You are trying a complex query; for a simple task.

INSERT INTO table1 (username, level) 
SELECT username, MAX(level)
FROM table2
GROUP BY username

For your second question, you need to use a JOIN clause.

INSERT INTO oitc_test (username, xpboost_expiration, level, exp, kills, games) 
SELECT o.username, o.xpboost_expiration, MAX(o.level), MAX(o.exp), os.kills, os.games 
FROM oitc o
INNER JOIN oitc_sum os
    ON o.username = os.username
GROUP BY o.username;

where I am considering that username is the common key in both the tables oitc and oitc_sum

Upvotes: 3

Related Questions