SQL

Question

SQL

jacobe

SSC Enthusiast

Points: 195
More actions
August 4, 2003 at 2:01 am

#82497

How can query from a table results based
on top 5 salaries and lowest 5 salaries.
Many thanks in advance and looking forward for the responses.
Jacobe

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

I'm not quite sure of the result you want, but you could try, for starters:

 

select * from

(

    select top 5 with ties * from employee

        order by salary desc

) top5

union

select * from

(

    select top 5 with ties * from employee

        order by salary 

) bottom5

order by salary

Cheers,

- Mark

Cheers,
- Mark

jacobe SSC Enthusiast Points: 195 More actions · Answer 2

Hello Mark

I tried with another query which is as follows

SELECT TOP 5 *

FROM table

However I am not getting the lowest 5. I tried with bottom, but it's not working

Thanks,

Jacobe

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
Hello Mark
I tried with another query which is as follows
SELECT TOP 5 *
FROM table
However I am not getting the lowest 5. I tried with bottom, but it's not working

what about

ORDER BY salary (DESC) ?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 4

Hello,

Like top 5, is there something called bottom 5 to fetch the lowest 5?

Thanks

Jacobe

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Jacobe,

quote:
Like top 5, is there something called bottom 5 to fetch the lowest 5?

to the best of my knowledge, there is no such keyword.

However, proper sorting yields the same result

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mccork SSC-Insane Points: 22288 More actions · Answer 6

Jacobe,

If you order by SALARY or SALARY ASC you'll get the lowest 5 salaries.

If you order by SALARY DESC you'll get the highest 5 salaries.

Cheers,

- Mark

Cheers,
- Mark

jacobe SSC Enthusiast Points: 195 More actions · Answer 7

Hello,

Yes that's correct,but my query is bit complicated and I cannot use desc or asc.Nevertheless I am use 2 different queries to fetch records.

Thanks,

Jacobe

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

quote:
Yes that's correct,but my query is bit complicated and I cannot use desc or asc.Nevertheless I am use 2 different queries to fetch records.

would you mind posting the whole query?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 9

Hello,

I woul like to do that, unfortunately, I am behind exchange server and hence cannot post any code here. I will try from my residence.

Thanks indeed,

Jacobe

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

quote:
I woul like to do that, unfortunately, I am behind exchange server and hence cannot post any code here. I will try from my residence.

does a simple copy and paste not work ???

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bruce_reid SSCrazy Points: 2419 More actions · Answer 11

Hi Marc,whatz with ties in the first query,what does it do.

SQLBill SSC Guru Points: 51440 More actions · Answer 12

Here's an example of what WITH TIES does...

DATA;

1

2

3

4

SELECT TOP 3

FROM data

ORDER BY numbers ASC

1

2

SELECT TOP 3 WITH TIES

FROM data

ORDER BY numbers ASC

1

2

3

Also, there isn't a BOTTOM # in SQL Server. You have to use the ORDER BY ..... DESC or ASC to get the proper order.

-SQLBill

mccork SSC-Insane Points: 22288 More actions · Answer 13

Hi SQLBill,

Actually, your example would return 1,2,2 in both cases.

An example that would demonstrate WITH TIES is only slightly different:

 

SELECT TOP 2 *

FROM data

ORDER BY numbers ASC



numbers     

----------- 

1

2

compared with:

 



SELECT TOP 2 WITH TIES *

FROM data

ORDER BY numbers ASC



numbers     

----------- 

1

2

2

Cheers,

- Mark

Cheers,
- Mark

SQLBill SSC Guru Points: 51440 More actions · Answer 14

Mark,

You're right.

I was SAD that day.

(Sleeping At Desk)

-SQLBill