How to get distinct rows (descending order) based on a column


Hi Team,

I have a table like this
id Bid status
1 1 init
2 2 active
3 1 expired
4 1 active
5 3 init

Now I would like to get all distinct rows on BId in descending order of id, so my result shoud be like

2 2 active
4 1 active
5 3 init

Is there any way to get this directly with the query?
I am using MySQL.


You can achieve it in this way

const datos = await ModelName.query()
                             .select('columnOne', 'columnTwo')
   						     .orderBy('id', 'DESC')
return datos


  • Use the model name associated to your table
  • Use the distinct() method to indicate which column must be filtered to non repeated values
  • Use the orderBy() method to indicate which column to organize after as second argument you can indicate ASC y DESC way
  • Finally to get records use fetch() method
  • To select more columns only use the select() method to indicate columns by it’s name
  • Now use the groupBY() method to group results and only show one of them , you can group by the BId column which in fact is the column to be filtered


Doing as you suggested only returns BId, but i need all the three columns. If i do select * it returns duplicate BId.


please check my update to my answer @mohammadsiddiqui