Finding the nth highest value for each location

I have a table that looks like this

StateCode      TransmitDate      TransmitAmount
CA      2013-01-02 00:00:00.000      3000.00
CA      2013-01-13 00:00:00.000      6000.00
CA      2013-04-01 00:00:00.000      13000.00
CA      2013-05-01 00:00:00.000      13000.00
CA      2013-07-01 00:00:00.000      20000.00
CA      2013-08-01 00:00:00.000      25000.00
CA      2013-10-01 00:00:00.000      30000.00
CA      2013-11-01 00:00:00.000      35000.00
FL      2013-01-02 00:00:00.000      180000.00
FL      2013-01-13 00:00:00.000      200000.00
FL      2013-04-01 00:00:00.000      4000.00
FL      2013-05-01 00:00:00.000      6000.00
FL      2013-07-01 00:00:00.000      3000.00
FL      2013-08-01 00:00:00.000      24000.00
FL      2013-10-01 00:00:00.000      23000.00
FL      2013-11-01 00:00:00.000      19000.00
AL      2013-01-02 00:00:00.000      3000.00
AL      2013-01-13 00:00:00.000      6000.00
AL      2013-04-01 00:00:00.000      13000.00
AL      2013-05-01 00:00:00.000      13000.00
AL      2013-07-01 00:00:00.000      20000.00
AL      2013-08-01 00:00:00.000      25000.00
AL      2013-10-01 00:00:00.000      30000.00
AL      2013-11-01 00:00:00.000      35000.00

I need to run a report that returns the highest and lowest transmitAmount for each state for the year 2013.  

This is my query to try and return the highest value

SELECT StateCode,TransmitAmount FROM
   ( SELECT StateCode,TransmitAmount,ROW_NUMBER() OVER(ORDER BY TransmitAmount desc) As RowNum
      FROM NewStateRevenue ) As A
    WHERE A.RowNum IN (1)

This is not working as I want as I need to query by statecode.  

Any help would be greatly appreciated.
sherbug1015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

armchair_scouseCommented:
I'm probably going to misinterpret what you are looking for... but presuming that you have a table called NewStateRevenue, with the fields StateCode and TransmitAmount, then to find the minimum and maximum values for each state, use an aggregate query, e.g.

SELECT NewStateRevenue.StateCode, Min(NewStateRevenue.TransmitAmount) AS MinTransmitAmount, Max(NewStateRevenue.TransmitAmount) AS MaxTransmitAmount
FROM NewStateRevenue
GROUP BY NewStateRevenue.StateCode

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sherbug1015Author Commented:
Thanks.
0
PortletPaulfreelancerCommented:
oops
0
PortletPaulfreelancerCommented:
you were actually not too far away from using row_number() effectively here.

you needed to include "partition by StateCode" - but you would have only got the maximum values the way it was being filtered.

Below is a method that will give you 4 measures:
Minimum, Median, Average (mean), and Maximum
STATECODE TA_MINIMUM  TA_MEDIAN    TA_AVERAGE   TA_MAXIMUM
AL        3000        20000        18125        35000
CA        3000        20000        18125        35000
FL        3000        19000        57375        200000
FL        3000        23000        57375        200000

Open in new window

from this query. Here row_number() is used twice in opposite sorting directions so we can locate the single 'midpoint' record of each state i.e. the (median) but we also calculate min/avg/max also using windowing functions "over (partition by StateCode".
SELECT
          StateCode
        , TA_minimum
		, TransmitAmount AS TA_Median
        , TA_average
        , TA_maximum
FROM (
	SELECT
          StateCode
		, TransmitAmount
		, ROW_NUMBER() OVER (
            PARTITION BY StateCode
			ORDER BY TransmitAmount DESC
			) AS TA_desc
		, ROW_NUMBER() OVER (
            PARTITION BY StateCode
			ORDER BY TransmitAmount ASC
			) AS TA_asc
        , MIN(TransmitAmount) OVER (
            PARTITION BY StateCode
			) AS TA_minimum
        , AVG(TransmitAmount) OVER (
            PARTITION BY StateCode
			) AS TA_average
        , MAX(TransmitAmount) OVER (
            PARTITION BY StateCode
			) AS TA_maximum
	FROM NewStateRevenue
	) AS A
WHERE (TA_asc = TA_desc OR TA_asc = TA_desc + 1) -- median is the 'higher' if even number
--	WHERE (TA_asc = TA_desc OR TA_asc = TA_desc - 1) -- median is the 'loower' if even number
	

Open in new window

http://sqlfiddle.com/#!3/e3cc93/7
sorry about the oops
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.