Best way to select row of a max record in group?

[frogcjn]

a table has a groupId field, and a value field

How to select the full row record the max value in the group using sequelize?

I can only figure it out with SQL:

SELECT 
    a.*
FROM
    table a
        INNER JOIN
    (SELECT 
        groupId, MAX(value) value
    FROM
        table
    GROUP BY groupId) b ON a.groupId = b.groupId
        AND a.value = b.value
GROUP BY a.groupId

and let sequelize run it.

const results = await sequelize.query(sqlSentence, { model: SiteContent })

[janmeier]

Currently we only support joins on relations defined in sequelize, and we don't have great subquery support either #2787

[frogcjn]

OK. Thanks. It's good enough.

[PatrickGeyer]

Any news on this @janmeier, is this still not possible?

[4sagar]

I managed to get the required result as below

Model.findAll({
        where: {
          id: { 
            [Sequelize.Op.in]: [Sequelize.literal('SELECT MAX(id) FROM a GROUP BY groupId')]
          }
        },
      });

[7hibault]

Still using a little bit of SQL, I think the following should work, using DISTINCT ON (official doc).

• order by your groupId
• order by the value on which you check the max
• select only the first row (the max because it is ordered) for each groupId

const results = await MyModel.findAll({
	attributes: [sequelize.literal('DISTINCT ON ("groupId") "MyModels"."groupId"'), 'value'],
	order: [['groupId', 'DESC'], ['value', 'DESC']]
})

[Salmandabbakuti]

I managed to get the required result as below

Model.findAll({
        where: {
          id: { 
            [Sequelize.Op.in]: [Sequelize.literal('SELECT MAX(id) FROM a GROUP BY groupId')]
          }
        },
      });

above query returning duplicate values too. is there any workaround to prevent duplicates using above query?
@yosagar