CONTRASTING COMPLEX SQL QUERIES AND RAILS ACTIVE RECORD QUERIES

By: Saurav

2018-02-04 09:15:00 UTC

Q1. Given an employee table, find the nth highest salary

SQL/MS SQL:

//Using subqueries
select salary from
(Select DISTINCT(salary) from employees
where
group by 
order by salary DESC
limit (n)) result
order by salary 
limit(1)

//Using CTE

WITH RESULT AS
{
  Select salary, DENSE_RANK() over (Order by Salary DESC) as DR
  from employees
}
Select salary 
from RESULT
where RESULT.DR = n
LIMIT 1

//Rails Active record query:

Employee.order('salary DESC').distinct.limit(n).last

Q2. Query to find department with the highest number of employees

//SQL

Select Departments.name
from Employees JOIN Departments on Employees.DepartmentID = Departments.DepartmentID
group by Departments.name
order by  COUNT(*) DESC

//Rails Active Record
// Assuming, there is an index in employee table which points to a department
Department.joins(:employees).select('departments.name').group("departments.name").order('count(employees.id) DESC').limit(1).pluck(:name)

to be contd.

Owned & Maintained by Saurav Prakash

If you like what you see, you can help me cover server costs or buy me a cup of coffee though donation :)