HELP WITH MIN() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

HELP WITH MIN()

Author

Topic

mindbender
Starting Member

1 Post

Posted - 2011-08-05 : 14:13:03

THis is the result I get from running the below query
CCT_NUM MAJOR_VERSION STEP_NUM TYPE VALUE
8 0 11 1 225
8 0 14 1 226
9 0 1 1 828
8 0 6 1 245

SELECT t.*
FROM cctvariable t
INNER JOIN (SELECT c.cct_num, c.value,MIN(c.step_num) AS First
FROM cctvariable c
GROUP BY c.cct_num, c.value)t1
ON t.step_num= t1.First
AND t.cct_num = t1.cct_num
AND t.value = t1.value
and t.cct_num in (8,9)
and t.major_version = 0
and t.type in (1)

What I want to return is the minimum step number and only the row with the associated value like below

CCT_NUM MAJOR_VERSION STEP_NUM TYPE VALUE
9 0 1 1 828
8 0 6 1 245

Any help woudl be appreciated driving me insane

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-05 : 14:38:35

I can't tell by your data but I'm guessing that you want this:


SELECT t.*
FROM   cctvariable t
INNER JOIN (
       SELECT c.cct_num
              ,MIN(c.step_num) AS [First]
       FROM cctvariable c
       where  c.cct_num in (8,9)
       and    c.major_version = 0
       and    c.type in (1)
       GROUP BY c.cct_num
       ) t1
       ON t.step_num= t1.[First]
       AND t.cct_num = t1.cct_num

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources