1

I have a table called Policy like:

Name     Version Id
Policy1  1.0.0   1
Policy2  2.0.0   2
Policy1  1.1.1   3
Policy2  2.0.1   4

I tried to extract the following:

Name     Version Id
Policy1  1.1.1   3
Policy2  2.0.1   4

based on one value per name and the highest version in the table.

I tried the following:

ActiveRecord::Base.connection.exec_query('(SELECT MAX(A.version) AS rev, A.name FROM policies A GROUP BY A.name) ')

If I add id to the mix like:

ActiveRecord::Base.connection.exec_query('(SELECT MAX(A.version) AS rev, A.name, A.id FROM policies A GROUP BY A.name) ')

I get the error:

ActiveRecord::StatementInvalid: Mysql2::Error: Expression #3 of SELECT             
list is not in GROUP BY clause and contains nonaggregated column     
'pikto_dev.A.id' which is not functionally dependent on columns in 
GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by: (SELECT MAX(A.version) AS rev, A.name, 
A.id FROM policies A GROUP BY A.name)`

I also tried things like:

Policy.select(:id, "MAX(version)", :name).group(:name)

Krishnakumar
  • 725
  • 1
  • 6
  • 11
Aravind
  • 1,391
  • 1
  • 16
  • 41
  • You'll have to either put `id` in `group by` clause or use aggregate function on `id`. – DEarTh May 02 '18 at 10:47
  • Your question has been asked and answered on Stack Overflow already. If the answers from the duplicate don't fully address your question please edit it to include why and flag this for re-opening. Thanks! – O. Jones May 02 '18 at 10:57

1 Answers1

2

If you want the id too you should join the aggregated result with the original table 

select t1.rev, t1.name, p.id  
from (
   SELECT MAX(A.version) AS rev, A.name
  FROM policies A 
   GROUP BY A.name ) t1
inner join  policies p on p.version = t1.rev and t1.name = p.name
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107