how to get top three salary getters from table employee

Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table

i tried this
select top 3 salary from employee order by salary desc

but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200

how can i do it

please help

thanks

You can run this query:

SELECT * FROM YourTable
WHERE Salary IN (
SELECT DISTINCT TOP 3 Salary FROM YourTable
ORDER BY Salary DESC )
ORDER BY Salary DESCsql

How to get the current User when using a general connection user

I have a VB.Net app and a SQL Server 2005 database. Users must login to use
the application, and I have an Employee table to store their details.
However, I use a common user ID to connect to the database (for reasons I
won't go into here).
My problem is, some of my triggers need to know who the current user is, and
of course I can't use the current connection information to get this as I
always get the common user ID.
Does anyone have a technique to solve this dilema? Maybe some way to set a
variable or something when I connect so that I can determine which user has
initiated the connection?You can use application roles to achieve that. Works something like this:
a) Every user has a Windows account using Windows Authentication
b) When they run the application you enable the application role
c) Inside the application's session you can look at what the username is,
including in triggers, using suser_sname()
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"David" wrote:

> I have a VB.Net app and a SQL Server 2005 database. Users must login to us
e
> the application, and I have an Employee table to store their details.
> However, I use a common user ID to connect to the database (for reasons I
> won't go into here).
> My problem is, some of my triggers need to know who the current user is, a
nd
> of course I can't use the current connection information to get this as I
> always get the common user ID.
> Does anyone have a technique to solve this dilema? Maybe some way to set a
> variable or something when I connect so that I can determine which user ha
s
> initiated the connection?
>

How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

Can you please assist me on how to get the 2nd record in case there are

3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get

the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?

-----------------------*--

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...

SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?

SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC

Gives you the second highest value of val.

If you want to do this for a set values, this may be more practical:

SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan