CODEWITHSUNDEEP
mysqlsql
Baskar
Baskar

Reputation: 1140

Get Maximum value of a column in mysql DB

My DB table column values are

tenant_ id  group_id
2           2-100
2           2-111
1           1-222
1           1-888
2           2-999 
2           2-1000

Query : 

select max(group_id) from prospect_group where tenant_id=2

I have used the above query to get the max value for tenant_id=2 but it returns the value as 999 instead of 1000. How to get 1000 as the max value.??? Can anyone help me..???

Upvotes: 6

Views: 7122

Answers (2)

John Woo
John Woo

Reputation: 263893

You need to have GROUP BY clause

SELECT tenant_ID, MAX(CAST(group_ID AS SIGNED))
FROM tableName
-- WHERE  tenant_id=2 -- uncomment this if you select only for specific tenant_ID
GROUP BY tenant_ID

try it by replacing to an empty char.

SELECT tenant_ID, 
       MAX(CAST(REPLACE(group_ID, CONCAT(tenant_ID, '-'), '')  AS SIGNED)) maxGID
FROM tableName
-- WHERE  tenant_id=2 -- uncomment this if you select only for specific tenant_ID
GROUP BY tenant_ID

SQLFiddle Demo

Upvotes: 5

xdazz
xdazz

Reputation: 160943

You need to add GROUP BY clause.

select max(group_id) from prospect_group where tenant_id=2 group by tenant_ id

Upvotes: 1

Related Questions