Finding the two biggest numbers?

  • Thread starter Thread starter Anders Juul
  • Start date Start date
A

Anders Juul

Dear Group.
I have a table with - among other things - names and salaries.
My problem is to return the TWO highest salaries:
Name Salary
---------------------
Person A 120.000
Person B 100.000
Person C 155.000
Person D 119.500

My query should return Person A and Person C
How can that be done?

(In MySQL it would be simple with a LIMIT-command, but that doesn't work
here...

Regards
Anders Juul
 
I have a table with - among other things - names and salaries.
My problem is to return the TWO highest salaries:
Name Salary
---------------------
Person A 120.000
Person B 100.000
Person C 155.000
Person D 119.500

My query should return Person A and Person C
Click to expand...

Hi Anders,

try

SELECT TOP 2
[Name],
Salary
FROM
yourtable
ORDER BY Salary DESC;

You have sorted your SELECT
query by Salary from highest to
lowest, then you are saying,
"return only the top 2."

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
SELECT TOP 2 [Name]
FROM YourTable
ORDER BY [Salary] DESC

If "Name" is the actual Field name in your Table, it is a bad choice for a
Field name since virtually every object in Access has a "Name" Property.
Suggest you change the Field name "Name" to something else, even
"PersonName".
 
It works!

But what is my question was to find the two highest salaries from each
department:
What if my table is:
Dept PName Salary
---------------------
1 Person A 120.000
1 Person B 100.000
2 Person C 155.000
2 Person D 110.000
2 Person E 119.500

Then the result should be
1 Person A
1 Person B
2 Person E
2 Person C

Do anyone have an idea about this?

Anders

Gary Walter said:
I have a table with - among other things - names and salaries.
My problem is to return the TWO highest salaries:
Name Salary
---------------------
Person A 120.000
Person B 100.000
Person C 155.000
Person D 119.500

My query should return Person A and Person C
Click to expand...

Hi Anders,

try

SELECT TOP 2
[Name],
Salary
FROM
yourtable
ORDER BY Salary DESC;

You have sorted your SELECT
query by Salary from highest to
lowest, then you are saying,
"return only the top 2."

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
I do use another Field name: "ADRNAVN". I use a Danish program, and in some
contexts I can use "Name" as a field name and in some other contexts not.
It's vice versa with the Danish translation "Navn"...

Thanks
Anders

Van T. Dinh said:
SELECT TOP 2 [Name]
FROM YourTable
ORDER BY [Salary] DESC

If "Name" is the actual Field name in your Table, it is a bad choice for a
Field name since virtually every object in Access has a "Name" Property.
Suggest you change the Field name "Name" to something else, even
"PersonName".

--
HTH
Van T. Dinh
MVP (Access)


Anders Juul said:
Dear Group.
I have a table with - among other things - names and salaries.
My problem is to return the TWO highest salaries:
Name Salary
---------------------
Person A 120.000
Person B 100.000
Person C 155.000
Person D 119.500

My query should return Person A and Person C
How can that be done?

(In MySQL it would be simple with a LIMIT-command, but that doesn't work
here...

Regards
Anders Juul
Click to expand...
Click to expand...
 
Sure Anders,

Do you have a single primary key field for
your table?

If so (say primary key field = "pk"),
then I believe this will be close to what
you want:

SELECT
t1.Dept,
t1.PName,
t1.Salary
FROM yourtable AS t1
WHERE t1.pk IN
(SELECT TOP 2 t2.pk
FROM
yourtable As t2
WHERE t2.Dept = t1.Dept
ORDER BY Salary DESC);

Anders Juul said:
It works!

But what is my question was to find the two highest salaries from each
department:
What if my table is:
Dept PName Salary
---------------------
1 Person A 120.000
1 Person B 100.000
2 Person C 155.000
2 Person D 110.000
2 Person E 119.500

Then the result should be
1 Person A
1 Person B
2 Person E
2 Person C

Do anyone have an idea about this?

Anders

Gary Walter said:
I have a table with - among other things - names and salaries.
My problem is to return the TWO highest salaries:
Name Salary
---------------------
Person A 120.000
Person B 100.000
Person C 155.000
Person D 119.500

My query should return Person A and Person C
Click to expand...

Hi Anders,

try

SELECT TOP 2
[Name],
Salary
FROM
yourtable
ORDER BY Salary DESC;

You have sorted your SELECT
query by Salary from highest to
lowest, then you are saying,
"return only the top 2."

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Back
Top