0

My database have a device reading list,

columns are id ,device_id ,device_reading ,update_time

How can I select all latest reading for each device?

that means,I need sort data according to update_time first

then I need to filter database use some unique method(unique device_id)

then the I can retrieve device_reading from rows filtered just now.

That is to say, retrieve column a from table where b is distinct

how can I achieve this? in ruby on rails

thanks in advance

pilcrow
  • 56,591
  • 13
  • 94
  • 135
asdjkag
  • 448
  • 1
  • 7
  • 23

2 Answers2

3

Supposing that model name is DeviceReading

DeviceReading.distinct(:device_id).order('update_time DESC').pluck(:device_reading)

You can use ASC in place of DESC if you want to sort by ascending order

Source: Distinct and pluck sort/order

EDIT: The distinct only works in rails 4 so if you are using rails 3 you should do something like this:

DeviceReading.select(:device_id).uniq.pluck(:device_reading)

However when I chain .order('update_time DESC') in above I get mysterious error. I am trying to find solution. meanwhile I Hope this helps you.

Source: distinct undefined error

Community
  • 1
  • 1
Hardik
  • 3,815
  • 3
  • 35
  • 45
  • when I use it error message says: undefined method `distinct',I use uniq instead,no more error message,but the result seems not filtered--still come out many in a mass – asdjkag Sep 02 '14 at 01:46
  • I have updated my answer. And I have also asked another question related to using `order` with `select` here : http://stackoverflow.com/questions/25616358/rails-3-query-getting-error-while-using-select-with-order – Hardik Sep 02 '14 at 05:21
1

This questions looks quite similar to the one below:

Select first row in each GROUP BY group?

In the case when no elegant solution can be found the Rails way, you can first figure out the correct sql and use find_by_sql to retrieve the dataset.

Community
  • 1
  • 1
dezhan
  • 50
  • 4
  • I won't recommend writing raw SQL in rails because... **(1)** this can be done with rails active-record helper as I have shown in my answer **(2)** When you change the database, you need to change the raw sql as per the new database's syntex – Hardik Sep 01 '14 at 10:27
  • @Uandl, thanks for the comments. I agree with you that it's not a best practice to directly use SQL in rails application. It should be the last choice with complex queries that Rails doesn't support. – dezhan Sep 01 '14 at 11:03