how to select 90% records from a table?

Question

Post reply

how to select 90% records from a table?

abhas

SSCarpal Tunnel

Points: 4681
More actions
February 16, 2011 at 3:59 am

#246706

Hi,
Can any one knows idea about how to select 90% records from table.
My scenario is that, i am having a table with coloumn salary.And i want to select 90% records having max salary.And after that 90% i have to select top 20 records with max salary.
thanks
abhas.

Viewing 5 posts - 1 through 4 (of 4 total)

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

mahesh.dasoni SSCommitted Points: 1653 More actions · Answer 1

For 90 % record use statement

SELECT TOP 90 PERCENT * from Table name

& for select top 20 rows just use

SELECT TOP 20 * from table name

Lowell SSC Guru Points: 323518 More actions · Answer 2

remember a TOP without order by doesn't make sense...

to do the top 20 of the top 90%, it's just nested selects....

select TOP 20 * FROM

(

SELECT TOP 90 PERCENT * FROM YOURTABLE ORDER BY SALARY DESC

) X

ORDER BY SALARY DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Lowell (2/16/2011)
remember a TOP without order by doesn't make sense...
to do the top 20 of the top 90%, it's just nested selects....
select TOP 20 * FROM
(
SELECT TOP 90 PERCENT * FROM YOURTABLE ORDER BY SALARY DESC
) X
ORDER BY SALARY DESC

I still don't see how that makes sense...

Select top 90% order by salary desc

That gives you the biggest salaries.

Then you select the top 20 of that dataset. That won't be any different than selecting the top 20 in the first place.

That could make some sort of sens if you used order by Salary ASC in the inner select to filter out the CEO and other big salaries and then pick the top 20 after those...

terrykzncs Ten Centuries Points: 1324 More actions · Answer 4

The nested example is a good example as an explanation.

In the real world we would change the ORDER BY clause for one of the select statements. For instance, when searching for the employees with the top salaries and then extracting those with longest service.:-)