For this schema:
http://github.com/metaperl/DBIx--Sho...MAX/schema.pdf

How would you get the top 5 salaries (and their department at the
time) such that the top 5 lists any employee only once?

My fear with my current query is that the GROUP BY will arbitrarily
reduce multiple instances of an employee to one row, without choosing
the row with the highest salary:

-- top 5 salaries, distinct by employees, and their department at the
time
SELECT
*
FROM
salaries INNER JOIN dept_emp USING (emp_no) INNER JOIN departments
USING (dept_no)
WHERE
salaries.to_date <= dept_emp.to_date
GROUP BY
emp_no
ORDER BY
salary DESC
LIMIT 5 ;

If you want to download the sample database in SQLite format, here's
how to do so:
#!/bin/bash -x

export TGZ=sampledb.tgz

wget http://sampledb.svn.sourceforge.net/...ar.gz?view=tar
mv sampledb.tar.gz* $TGZ
tar xvfz $TGZ

On 12 Aug, 21:26, metaperl <metap... (AT) gmail (DOT) com> wrote:
I'm not sure what GROUP BY emp_no is supposed to mean (in this
context). Assume the following table:

create table T (x int not null, y int not null, primary key (x,y));
insert into T (x,y) values (1,1), (1,2);

What would be the result of:

select x, y from T group by x;

?

Start by figuring out how to get the top 5 salaries from the salary
table


/Lennart

On Aug 13, 1:43*am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

An employee can have many salaries in the salaries table. I only care
about one salary per employee, so I did a GROUP BY. But because I care
about the highest salary of an employee, I am not sure which salary
row will be retained in the GROUP BY employee_id

Assume the following table:
Thats just the point. I'm not sure I have a guarantee on that. And I
know that I want the (1,2) row, not the (1,1) row.


SELECT * FROM salaries ORDER BY salary DESC LIMIT 5;

but this might list the same employee multiple times... take
Microsoft. Bill Gates might have 5 different salaries, but they might
always the highest. I only want 1 salary from any particular employee.
And I want it to be his highest.

Terrence Brannon wrote on 13.08.2009 15:34:
SELECT emp_no,
max(salary)
FROM salaries
GROUP BY emp_no
ORDER BY 2 DESC
LIMIT 5

On Aug 13, 10:08*am, Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com>
wrote:

Ok, thanks a lot. I'd forgotten about that aspect of GROUP BY