1. Registered User
Join Date
Mar 2004
Location
Maryland
Posts
152

I have a table sal with the following structure

emp_no int,
dept_no int,
basic_salary money

Is it possible to extract 7 highly paid employed with one SQL statement?

any help will be highly appreciated.

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
select TOP 7
emp_no,
dept_no,
basic_salary
from YourTable
order by basic_salary desc

3. Registered User
Join Date
Mar 2004
Location
Maryland
Posts
152

## top 7

how will it calculate highest salary? I mean how system knows to display record based on salary field?

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
"order by basic_salary desc"

5. 12 Monkey Method
Join Date
Feb 2004
Location
San Antonio, TX
Posts
565
the top clause retrieves the rows that would exist at the "Top" of your result set so if your result set was
joe
steve
bill
rob
mary

and you selected the top 3
you would get
joe
steve
bill

the control factor with the top clause is the order by clause
the order by clause sorts the result set either in ascending (ASC) or descending (DESC) order. so if you sorted a salary column asc, the lowest salary would be at the top correct?. and if you selected the top 7 salaries in that example, you would have the 7 lowest salaries.
by sorting the salary column in desc order, you would get the top 7 salaries

Books Online{Limiting Result Sets Using TOP and PERCENT}

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by Mr_roomi
how will it calculate highest salary? I mean how system knows to display record based on salary field?

What is the context of the salary table? Does it hold weekly salary data?

Everyone here thinks it's annual.

7. 12 Monkey Method
Join Date
Feb 2004
Location
San Antonio, TX
Posts
565
Sheesh!

Code:
```create table sal
(
emp_no int
,dept_no int
,basic_salary money
)

insert into sal values (1 ,10,1000.00)
insert into sal values (2 ,10,2000.00)
insert into sal values (3 ,10,1500.00)
insert into sal values (4 ,10,1200.00)
insert into sal values (5 ,10,1000.00)
insert into sal values (6 ,10,3000.00)
insert into sal values (7 ,10,2200.00)
insert into sal values (8 ,10,1250.00)
insert into sal values (9 ,10,1350.00)
insert into sal values (10 ,10,1000.00)

select * from sal

select top 7 emp_no, dept_no
,Basic_Salary as 'WeeklySalary' --by week
,BiWeeklySalary=(Basic_Salary*2) --BI week
,AnnualSalary=((basic_salary*2)*26) --Annual
from sal
order by AnnualSalary desc```

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
OK...so what if there's more than 1 salary row per employee?

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...an aggregate query to sum up the salary values.

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by blindman
...an aggregate query to sum up the salary values.

Thanks you...my point...

Without the DDL of the table we'd be just shooting in the dark

Bang

Yo blind dude..did that hit you?

11. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Nyah nyah, ya missed me!

You forget that, as the Blindman, I shoot in the dark just fine!

12. 12 Monkey Method
Join Date
Feb 2004
Location
San Antonio, TX
Posts
565
okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.

13. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
"since the poster hasnt said anything about it then this post is officially dead"???

Like THAT has ever stopped us before...

and "malingerer"?

In the words of Inigo Montoya -
"You keep using that word. I do not think it means what you think it means."

http://dictionary.reference.com/search?q=malingerer

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
you killed my father -- prepare to die!

15. 12 Monkey Method
Join Date
Feb 2004
Location
San Antonio, TX
Posts
565
i know exactly what it means.