15,881,172 members
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
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!

## 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```

## 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.

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 ?

## 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)[^]

v3
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)