Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
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
Updated 10-Mar-16 19:49pm
v3
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!

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
 
Share this answer
 
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.
 
Share this answer
 
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 ?
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)[^]
 
Share this answer
 
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...
Select * from tablename
Where salary = (Select Max(Salary) from tablename)
 
Share this answer
 
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?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900