question

n3w2sql avatar image
n3w2sql asked

selecting the top 10 and bottom 10

I am trying to do some analysis for the max and min 10 limits for each of my departments for each month but the only way I can think of is to use the code below then combining them together but it seems long winded and I dont think it will be very effective is there another way of doing this? many thanks in advance. SELECT top 10 [POLICYNUM] ,[INCPDT] ,SUBSTRING(incpdt,6,2)[MONTH] ,[LIMIT] ,[Narrative] ,[SYSTEM] FROM [LEE].[dbo].[DLMTIVS2] where Narrative = 'Construction' order by narrative, SUBSTRING(incpdt,6,2), LIMIT desc select top 10 [POLICYNUM] ,[INCPDT] ,SUBSTRING(incpdt,6,2)[MONTH] ,[LIMIT] ,[Narrative] ,[SYSTEM] FROM [LEE].[dbo].[DLMTIVS2] where Narrative = 'Construction' order by narrative, SUBSTRING(incpdt,6,2), LIMIT asc
sql-server-2008t-sql
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Could you supply some example data?
2 Likes 2 ·
Thanks for the quick response, below is some sample data from the tablewhich has about 1000 rows of data. Sorry if I wasnt completly clear but will this code also work for my other depts as I need to get the max and min for each dept and month. POLICYNUM INCPDT MONTH LIMIT Narrative SYSTEM AFF139127A12 2012-01-25 01 15519482729 Construction DLM AFF140467A12 2012-01-24 01 1400500398.8 Construction DLM AFP141555A12 2012-01-31 01 372948443 Construction DLM AFD117659A12 2012-01-01 01 285012473.82 Construction DLM
0 Likes 0 ·
@Blackhawk-17 +1. The best help can be given when DDLs, test data and desired output is given? Chance are minimal for syntax and logical errors then.
0 Likes 0 ·
Sorry please ignore the month column as this is not in the table as I must have copied over the data including the month. The output I want is the top and bottom 10 limits for each month and for each dept.( i have 5 depts i need this for). Thanks again and sorry for not stating this to begin with.
0 Likes 0 ·
THanks for taking the time to go through this with me. Here is some more sample data from the table I have selected 1 row for each dept. What I want is the 10 max limits and 10 min limits for each dept and for each month I unioned my depts so that all the depts are together. Below is the sample. POLICYNUM INCPDT LIMIT Narrative SYSTEM ACN103390C12 2012-03-01 15190409540 CONSTRUCTION DLM ACN103552C12 2012-03-01 9512556 PROPERTY OTHER DLM ACP094654D12 2012-02-01 3661946438 Property DLM ACR038092K12 2012-05-01 110000000000 ENERGY DLM ACR038092K12 2012-05-01 2480741830 ENERGY OTHER DLM
0 Likes 0 ·
Have you tried my edited answer? I guess it should give you the desired result.
0 Likes 0 ·
Don't worry... we'll ask for what we need. It just helps both us and you when as much detail as possible is passed along as early as possible.
0 Likes 0 ·
Show more comments
Usman Butt avatar image
Usman Butt answered
Since you need month wise limits, I guess the following could be according to the need ; WITH CTE AS ( SELECT [POLICYNUM] , [INCPDT] , SUBSTRING(incpdt, 6, 2) [MONTH] , [LIMIT] , [Narrative] , [SYSTEM] , ROW_NUMBER() OVER (PARTITION BY SUBSTRING(incpdt, 6, 2) ORDER BY Limit ) LimitAsc , ROW_NUMBER() OVER (PARTITION BY SUBSTRING(incpdt, 6, 2) ORDER BY Limit DESC ) LimitDesc FROM [LEE].[dbo].[DLMTIVS2] WHERE Narrative = 'Construction' ) SELECT * FROM CTE WHERE LimitAsc BETWEEN 1 AND 10 OR LimitDesc BETWEEN 1 AND 10 EDIT: ; WITH CTE AS ( SELECT [POLICYNUM] , [INCPDT] , SUBSTRING(incpdt, 6, 2) [MONTH] , [LIMIT] , [Narrative] , [SYSTEM] , ROW_NUMBER() OVER (PARTITION BY Narrative, SUBSTRING(incpdt, 6, 2) ORDER BY Limit ) LimitAsc , ROW_NUMBER() OVER (PARTITION BY Narrative, SUBSTRING(incpdt, 6, 2) ORDER BY Limit DESC ) LimitDesc FROM [LEE].[dbo].[DLMTIVS2] --WHERE Narrative = 'Construction' NOW NEEDING ALL THE DEPARTMENTS ) SELECT * FROM CTE WHERE LimitAsc BETWEEN 1 AND 10 OR LimitDesc BETWEEN 1 AND 10
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@n3w2sql Since you already have the MONTH column, then why are you using SUBSTRING to extract the same? Is it different?
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
This is a translation to get both results in one run through using a CTE and the windowing function ROW_NUMBER(): ;WITH PreppedData AS (SELECT [POLICYNUM], [INCPDT], SUBSTRING(incpdt, 6, 2) [MONTH], [LIMIT], [Narrative], [SYSTEM], ROW_NUMBER() OVER (ORDER BY narrative, SUBSTRING(incpdt, 6, 2), Limit) MinLimit, ROW_NUMBER() OVER (ORDER BY narrative, SUBSTRING(incpdt, 6, 2), Limit DESC) MaxLimit FROM [LEE].[dbo].[DLMTIVS2] WHERE Narrative = 'Construction') SELECT * FROM PreppedData WHERE Minlimit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

n3w2sql avatar image
n3w2sql answered
Thank you Usman and everyone else for all your time and help now I can understand the logic behind this a lot better and it has fixed what I was trying to do. works perfectly. Thanks again
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@n3w2sql Thanks for letting us know. Please mark the helping answer as accepted, which would help the future users as well.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.