How do i get maximum salary full row

Question

1.00/5 (2 votes)

See more:

I NEED TO GET MAX SALARY HOLDER NAME, ID ,,,,THAT FULL ROW.

What I have tried:

SELECT * FROM AVI_02Mar2016 WHERE SALARY = (SELECT Max(SALARY ) FROM AVI_02Mar2016)

Posted 10-Mar-16 0:52am

Member 12119075

Updated 10-Mar-16 19:49pm

John C Rayan

v3

Add a Solution

Comments

Jörgen Andersson 10-Mar-16 7:05am

And your problem is?

Animesh Datta 10-Mar-16 7:12am

what is your issue ?

CHill60 10-Mar-16 7:30am

Nothing wrong with your query that I can see.
Please don't tell me that you have a table per month of the year
And don't SHOUT!

4 solutions

Solution 3

And just for the hell of it yet another variation without any of this new-fangled CTE and ROWNUMBER stuff. It's not very different from the original, it uses a derived table rather than a sub-select instead.

What this (and the poster's sub-select version) do that the ROWNUMBER and CTE solutions don't (appear) to do is return all employees on the maximum salary not just the first one. You never know, there might be two or more very keen bods on the sales team. :)

SQL

select * from avi_ddMMMyy
inner join (select max(salary) as salary from
            avi_ddMMMyy) as maxima
on maxima.salary = avi_ddMMMyy.salary

Posted 10-Mar-16 19:34pm

cigwork

Solution 1

You have to use ROW_NUMBER() ranking function.

SQL

SELECT t.*
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rn
    FROM YourTable
) AS t
WHERE t.rn = 1

If you want to get more information about ranking functions, please see: Ranking Functions (Transact-SQL)[^]

Posted 10-Mar-16 2:19am

Maciej Los

Updated 10-Mar-16 4:21am

v3

Comments

CHill60 10-Mar-16 8:46am

Couple of typos ... you've lost the FROM AVI_02Mar2016 from the SELECT and shouldn't that be WHERE t.rn = 1?

Maciej Los 10-Mar-16 10:22am

Thank you, Caroline. The answer has been improved.
Hurry...

Solution 4

Select * from tablename
Where salary = (Select Max(Salary) from tablename)

Posted 10-Mar-16 19:49pm

subbuksr123

Comments

CHill60 11-Mar-16 20:24pm

Er.. that will be the OP's query posted in the question... was this meant to be a solution?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-03-10T02:45:00

Solution 2

Your query works as it is.

Simpler would be

SQL

SELECT TOP 1 * FROM AVI_02Mar2016 ORDER BY SALARY DESC

If you are trying to get the maximum salary from a set of tables then you can use a CTE

SQL

;WITH AllTables AS
(
	SELECT TOP 1 * FROM AVI_02Mar2016 ORDER BY SALARY DESC
	UNION ALL
	SELECT TOP 1 * FROM AVI_02Apr2016 ORDER BY SALARY DESC
	-- UNION ALL ... all of your other tables
)
SELECT TOP 1 * FROM AllTables ORDER BY SALARY DESC

Even better would be to have a single table AVI and have an extra column for the date.

Posted 10-Mar-16 2:45am

CHill60

Comments

Maciej Los 10-Mar-16 10:27am

Good point!
BTW: Why OP has to use CTE to get result from set of tables? Does simple UNION ALL wouldn't be enough?

CHill60 10-Mar-16 10:31am

It's the wanting the TOP 1 that causes the fun. Of course another alternative would be
select TOP 1 * FROM (
SELECT TOP 1 * FROM AVI_02Mar2016
UNION ALL
SELECT TOP 1 * FROM AVI_02Apr2016
-- UNION ALL ... etc) AS C
But you know what I'm like for CTEs :-)

Maciej Los 10-Mar-16 10:44am

Yeah, CTE rules! Thank you for clarification.

Member 12119075 11-Mar-16 1:59am

thank u

Member 12119075 11-Mar-16 3:58am

if i need like max salary and minimum age , what shall i do ?