Iam trying to select 2 values from a Table, Employee emp_name, emp_location grouping by emp_location, iam aware that the columns which are in group by function needs to be in select clause, but i would like to know whether is there any other way to get these value in a single query.
My intention is to select only one employee per location based on age. sample query
select emp_name,emp_location
from Employee
where emp_age=25
group by emp_location
please help in this regard.
Thanks a lot for all the guys who have responded for this question. I will try to learn these windows functions as these are very handy.
The reason why this works in MySQL and not in Oracle, is because in Oracle, as well most other databases, you either need to specify a field (or expression) in the group by clause, or it has to be an aggregation which combines the values of all values in the group into a single one. For instance, this would work:
select max(emp_name),emp_location
from Employee
where emp_age=25
group by emp_location
However, it's may not the best solution. It will work if you want just the name, but you'll get into trouble when you want to have multiple fields for an employee. In that case max won't do the trick. In the query below, you might get a first name that doesn't match the last name.
select max(emp_firstname), max(emp_lastname), emp_location
from Employee
where emp_age=25
group by emp_location
On solution for this, is using a window function (analytical function). With those, you can generate a value for each record, without immediately reducing the number of records. For instance, with a windowed max function, you could select the max age for people named John, and display that value next to every John in the result, even if they don't have that age.
Some functions, like rank, dense_rank and row_number can be used to generate a number for each employee, which you can then use to filter by. In the example below, I created such a counter per location (partition by), and ordered by, in this case name and id. You can specify other fields as well, for instance if you want one name per age per location, you specify both age and location in partition by. If you want the oldest employee of each location, you can remove where emp_age=25 and order by emp_age desc instead.
select
*
from
(select
emp_name, emp_location,
dense_rank() over (partition by emp_location order by emp_name, emp_id) as emp_rank
from Employee
where emp_age=25)
where
emp_rank = 1