CODEWITHSUNDEEP
mysql
someone
someone

Reputation: 137

MySql: Group By Select specific value or max

lets say I have a table with id and version. I am grouping by id. For each id, if version = 2, then fetch that row else fetch MAX(version).

+----+---------+
| id | version |
+----+---------+
|  1 |       3 |
|  1 |       2 |
|  2 |       1 |
|  2 |       3 |
+----+---------+

Should fetch

+----+---------+
| id | version |
+----+---------+
|  1 |       2 |
|  2 |       3 |
+----+---------+

Query should be something like:

select id, version(2 if exists for id or max) from table group by id;

I would prefer not to use union since it's a huge query in reality with multiple joins. I just used a simpler example to get the point across.

Possible Solution: 

select * from (select * from table order by version = 2 desc, version desc) as t1 group by id

Upvotes: 1

Views: 574

Answers (4)

xiongji
xiongji

Reputation: 89

Try this:

SELECT * FROM(SELECT id,versioin from table1 where version=2 
union 
SELECT id,max(version) as version FROM `table1 ` GROUP BY version) as A 
group by version

or

SELECT id,versioin from table1 where version=2 
union 
SELECT id,max(version) as version FROM `table1 ` 
where id not in(SELECT id from table1 where version=2 ) GROUP BY version

Upvotes: 0

jmail
jmail

Reputation: 6134

look here:

SELECT id,ver
FROM table1
WHERE ver = 
(SELECT MAX(ver) FROM table1 WHERE ver < (SELECT MAX(ver) FROM table1))
UNION
SELECT id,(MAX(`ver`)) FROM table1 WHERE id=2;

sqlfiddle: http://sqlfiddle.com/#!2/b7f65/1

Upvotes: 0

Leo Zhao
Leo Zhao

Reputation: 544

1.Fetch rows that version=2;

2.Fetch max version rows that id not in version=2 rows;

3.Unionize them.

And try this:

SELECT id, version FROM table WHERE version = 2
UNION
SELECT id, MAX(version) version FROM table WHERE id not in 
(SELECT id FROM table WHERE version = 2)
GROUP BY id

SQL Fiddle

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

select id, version from table 
where id=2 or version in 
    (select max(version) from table 
        where id!=2  group by id)

Upvotes: 0

Related Questions