find out the next top 10 that does not belong to the first top 10

Question

find out the next top 10 that does not belong to the first top 10

karthik82.vk

SSCrazy

Points: 2002
More actions
August 31, 2014 at 4:57 am

#290294

Hi,
I have a data of 100 PostalCodes and parcels associated with it. Each postal code will be having multiple parcels. My aim is to find the count of parcels for the postal codes and display them in a web page by a count of 10 rows.
I have a problem in displaying the postal codes and its count in sets of 10. The data will be picked based on the count from Higher to Lower postalcodes based on its count.
The query I used is
select top 10 postalcode, count(parcels) as Cnt
from TableParcel
group by postalcode
order by Cnt desc
This code will be displaying the first 10 postalcodes and will be displayed in the web page.
But I want to find out the next top 10 postal codes that does not belong to the first top 10 postal codes.
How can I do this?
Any help is highly appreciated.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

karthik82.vk (8/31/2014)
Hi,
I have a data of 100 PostalCodes and parcels associated with it. Each postal code will be having multiple parcels. My aim is to find the count of parcels for the postal codes and display them in a web page by a count of 10 rows.
I have a problem in displaying the postal codes and its count in sets of 10. The data will be picked based on the count from Higher to Lower postalcodes based on its count.
The query I used is
select top 10 postalcode, count(parcels) as Cnt
from TableParcel
group by postalcode
order by Cnt desc
This code will be displaying the first 10 postalcodes and will be displayed in the web page.
But I want to find out the next top 10 postal codes that does not belong to the first top 10 postal codes.
How can I do this?
Any help is highly appreciated.
Thanks.

Quick suggestion, use OFFSET FETCH to page the results.

😎

karthik82.vk SSCrazy Points: 2002 More actions · Answer 2

Hi,

Thanks for your suggestion. OFFSET FETCH Clause supports only 2012 and later. I want to perform this operation in earlier versions of SQL too...

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

If I remember correctly, Itzik Ben-Gan wrote an article on paging result set, both with and without offset fetch, suggest you look at it.

😎

Edit: added link.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Without knowing what "earlier versions" refer to here's a solution using ROW_NUMBER

select postalcode, count(parcels) as Cnt, ROW_NUMBER() OVER(PARTITION BY postalcode ORDER BY count(parcels) DESC ) as Ranking

from TableParcel

group by postalcode

order by Cnt desc

You could then get the next 10 postalcodes by placingthis query in a CTE and query the cte with WHERE Ranking > 10 and Ranking <=20

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

karthik82.vk SSCrazy Points: 2002 More actions · Answer 5

Hi Lutz,

Thanks for your reply.

When I ran your Query, i'm getting the rank value as 1 for all the values.

Below is the query that I used.

With T(PostalCode, Cnt, Ranking) --Column names for Temporary table
AS
(
select PostalCode, count(parcels) as Cnt, ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY count(parcels) DESC ) as Ranking
from PostTable
group by PostalCode
)
SELECT top 5 * FROM T
WHERE T.Ranking > 2 and Ranking <=5

LutzM SSC Guru Points: 107049 More actions · Answer 6

I'm sorry, my mistake.... (maybe due to the missing table def and sample data...)

select postalcode, count(parcels) as Cnt, ROW_NUMBER() OVER(ORDER BY count(parcels) DESC ) as Ranking

from TableParcel

group by postalcode

order by Cnt desc

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

-- similar solution but with page numbers...may give you some ideas

-- test data

SELECT TOP 1000000

CodeID = 1 + CAST(Rand(Checksum(Newid())) * 100 AS INT)

INTO #CodeData

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

--solution idea

declare @RPP as INT -- number of rows per page

set @RPP = 10

SELECT

CodeId

, cnt

, page_no

FROM (

SELECT

CodeID

, COUNT(CodeID)AS cnt

, CEILING((ROW_NUMBER()OVER(ORDER BY COUNT (codeid) DESC) - 1) / @RPP) + 1 AS page_no

FROM #CodeData

GROUP BY CodeID

) x

--WHERE page_no = 8

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

j-1064772 SSCertifiable Points: 5316 More actions · Answer 8

Unless, I missed something in understanding the EXCEP statement,

Why not simply use something like

SELECT TOP 20 * FROM PostCode

EXCEPT

SELECT TOP 10 * FROM PostCode