Tuesday, August 11, 2009

Get percentage of records from a table

Have you ever come across a situation where you need to get top  25%
records from a table. Here is the SQL that returns first 25% of records from a table.

SELECT TOP 25 PERCENT * 
FROM dbo.yourtable

This will return what you want, the 25% of records from the table. What if you want the second 25% of records.

SELECT TOP 50 PERCENT *
FROM ( SELECT TOP 50 PERCENT * 
FROM dbo.yourtable) results ORDER BY 1 DESC


On the above query by descending the records by first column, assuming that it is the primerykey or the records we need to filter by, you will get the top  25% which is the second 25% of the records.

Think how to get the third and the last 25% records leisurely.

4 comments:

Tharaka's Blog said...

3rd 25%

SELECT TOP 25 PERCENT *
FROM ( SELECT TOP 75 PERCENT *
FROM dbo.yourtable) results ORDER BY 1 DESC


4th 25%

SELECT TOP 25 PERCENT *
FROM dbo.yourtable
ORDER BY 1 DESC

Hope i am right... Keep updating the blog pal :)

Sheen said...
This comment has been removed by the author.
Sheen said...

Tharaka small change needs to be done in yor query. As in your query it will return 25% from the 75% of the sub query. In the sub query you have to take 50% of whole table in descending order which will give you 2nd 50% of the records and then select 50% from that sub query.

3rd 25%

SELECT TOP 50 PERCENT *
FROM ( SELECT TOP 50 PERCENT *
FROM dbo.yourtable ORDER BY 1 DESC) results ORDER BY 1

4th 25%
The query you wrote is correct but it will return a descending order result.

SELECT * FROM (SELECT TOP 25 PERCENT *
FROM Customer ORDER BY 1 DESC) results ORDER BY 1

Thnx mate

Tharaka said...

My mistake... Agree with you machan :)

My Achievements

Member of

Blog Archive

Followers

free counters