Query help regarding selecting the second highesh value

• Smash125

  Hall of Fame

  Points: 3562

  More actions

  August 2, 2012 at 11:09 am

  #265075

  Following Query gives details about TotalDue based on tblContactID . What i want is select second highest TotalDue for each tblContactID,if there is only one tblContactID select the first

  CREATE TABLE #Test

  (

  TotalDue MONEY,

  tblContactID INT

  )

  INSERT INTO #Test

  SELECT '100','1'

  UNION ALL

  SELECT '200','1'

  UNION ALL

  SELECT '300','1'

  UNION ALL

  SELECT '400','2'

  UNION ALL

  SELECT '500','3'

  UNION ALL

  SELECT '600','4'

  UNION ALL

  SELECT '700','4'

  UNION ALL

  SELECT '800','5'

  Output should like as below

  totalDue tblContactID

  2001

  4002

  5003

  7004

  8005

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 2, 2012 at 11:21 am

  #1521029

  row_number() is your friend in this case.

  that will allow you to rank/assign a value to each row:

  SELECT ROW_NUMBER() OVER (PARTITION BY tblContactID ORDER BY tblContactID,TotalDue) AS RW,* FROM #TEST

  then with that query, you could wrap it with an outer query, and get just the items that has RW=2

  SELECT * FROM (

  SELECT ROW_NUMBER() OVER (PARTITION BY tblContactID ORDER BY tblContactID,TotalDue) AS RW,* FROM #TEST

  )MyAlias

  WHERE RW =2

  BUT in your case, you have data where there is not a second row number, so you need to group the data differently, like this:

  SELECT MAX(TotalDue),tblContactID

  FROM (

  SELECT ROW_NUMBER() OVER (PARTITION BY tblContactID ORDER BY tblContactID,TotalDue) AS RW,* FROM #TEST

  )MyAlias

  WHERE RW <=2

  GROUP BY tblContactID

  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!