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


#1

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.


#3

You can achieve it in this way


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

CLARIFICATIONS

  • 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

#4

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


#5

please check my update to my answer @mohammadsiddiqui