Aggregate query problem

Question

Post reply

Aggregate query problem

Graham Daly

Valued Member

Points: 68
More actions
November 22, 2007 at 6:58 am

#181321

Hi, I have a resultset derived from two non-relational tables. This resultset can best be described as "possible matches". There are 3 distinct values for colA. I'm trying to boil this resultset down into 3 records by finding the maximum value in colB for each value in colA where the value of colB does not exceed the next highest value in colA. I hope this makes sense.
colA colB
549000549010
549000560540
549000560660
549000560680
549000560820
549000560840
549000564320
549000564340
549000568220
549000568280
549000568330
549000571170
549000582660
568210568220
568210568280
568210568330
568210571170
568210582660
571160571170
571160582660
The result I'm looking for is:
colA colB
549000564340
568210568330
571160582660
I'm new to SQL 2005 and I'm wondering if a recursive CTE is the way to go. I've been at this for a couple of hours this morning, with no real progress.
Many thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

Maybe this?

WITH CTE AS(

SELECT a.colA,a.colB

FROM MyTable a

WHERE NOT EXISTS(SELECT * FROM MyTable b

WHERE b.colA>a.colA AND b.colB<=a.colB))

SELECT a.colA,a.colB

FROM CTE a

WHERE NOT EXISTS(SELECT * FROM CTE b

WHERE b.colA=a.colA AND b.colB>a.colB)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Graham Daly Valued Member Points: 68 More actions · Answer 2

Thanks very much. That worked a treat. I modified the final SELECT to aggregate, as it is easier to read.

SELECT colA, MAX(colB) FROM CTE GROUP BY colA

Thanks again!

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

Maybe it was just by chance, but the result set you posted originally showed the "middle of the pack" for column B... the aggregate you settled on won't do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Graham Daly Valued Member Points: 68 More actions · Answer 4

Hi Jeff,

Thanks for the reply. I dont understand what you mean. I thought the first part of the correlated subquery gave me what I was looking for in setting the correct bounds. All I then had to do was the simple aggregation after the fact. Please forgive my naivity/stupidity: I'm not great with correlated subqueries or CTE's.

Thanks in advance for you patience.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 5

My bad... didn't read your post correctly... didn't realize the code that you posted was a snippet that would be used with the CTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)