Sequelize mssql: order by primary key and limit

Question

I want to run a query with results sort using the primary key and also limit the number of return results. For example:

return Things.findAll({ attributes: [ 'id', 'status', 'otherField' ], limit: 2, order: [['id', 'DESC']] })

when the query is build, it generate the following SQL statement:

... ORDER BY [Source].[id] DESC, [id] OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

Because id is the primary key and the sort parameter is also id I get the following error:

'A column has been specified more than once in the order by list. Columns in the order by list must be unique.'

I am using sequelize 3.30.4 with tedious 2.0.0 connecting to a Microsoft SQL server 2017.

Thank you.

same problem, did you manage to solve it? – Victor Ivens Jun 26 '18 at 13:46 — Victor Ivens, Jun 26 '18 at 13:46

score 0 · Answer 1 · answered Aug 24 '17 at 18:38

0

The order array should contain arrays/tuples. Try this:

return Things.findAll({
  attributes: [
    'id',
    'status',
    'otherField'
  ],
  limit: 2,
  order: [ 
    ['id', 'DESC']
  ]
})

answered Aug 24 '17 at 18:38

ivo

My bad, I made a mistake when typing my question. My code does use tuples. I edited the question – Jerome Kelly Aug 24 '17 at 18:47

score 0 · Answer 2 · answered Aug 12 '19 at 07:33

By the default, Sequelize create createdAt column in each table. So you can handle this issue by doing like this:

return Things.findAll({
  attributes: [
    'id',
    'status',
    'otherField'
  ],
  limit: 2,
  order: [ 
    ['createdAt', 'DESC']
  ]
})

Do the sorting by using column createdAt as the param, the sorting result have same result as you do with id column

2 Answers2