max values and rest of fields - 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

SQL Server 2000 Forums

SQL Server Development (2000)

max values and rest of fields

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-10-29 : 14:26:53

I have columns A, B C
C is the attempt #, A is the item. and B is the status.
I am trying to find the items with the highest test failures.
( which would be highest C where B not pass.

When I use this query I get

SELECT max(c)
FROM table1
WHERE B <> 'PASS' AND C>1
GROUP BY A; - I get 8 records.

but then I dont know which max record applies to which item A.

But this query
gets me 16 records - not just the max of C.

SELECT *
FROM table1
WHERE B <>'PASS' and C in ( select max(c) from table1 where B <> 'PASS' and C > 1 group by A )
ORDER BY A;

I'd like to share my table but don't see a way to attach it...
Thanks

andrewcw

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-29 : 18:49:52

Does this not work for you?


select A, max(C)
from table1
where B <> 'PASS' and C > 1
group by A
order by 2

Nathan Skerl

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-10-30 : 00:55:41

Thanks - it works and is much cleaner ( easier to follow )

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources