Skip to content

Instantly share code, notes, and snippets.

@kokorolx

kokorolx/Select distinct and order.md

Created March 7, 2021 17:00
Show Gist options
  • Save kokorolx/583dbaaab610e805fc7ebfdcf875d621 to your computer and use it in GitHub Desktop.
Save kokorolx/583dbaaab610e805fc7ebfdcf875d621 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Select distinct and order.md

-- Select last 5 record which is unique by province_name and order by created_at DESC

#
# Table name: listings
#
#  id                   :uuid             not null, primary key
#  province_name        :string
#  province_id          :uuid
#  created_at           :datetime         not null
#
Listing.find_by_sql("
SELECT province_name, max(created_at) as create_max
  FROM listings
  group by province_name
  order by create_max DESC
  limit 5
  ")

result:

"Bình Dương"	"2020-07-07 17:18:56.889321"
"Tp Hồ Chí Minh"	"2020-07-07 17:18:56.761668"
"Hà Nội"	"2020-07-07 17:18:56.635052"
"Đồng Nai"	"2020-07-07 17:18:55.57164"
"Đà Nẵng"	"2020-07-07 17:18:54.326533"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment