Group by clause problem - 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

General SQL Server Forums

New to SQL Server Programming

Group by clause problem

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-07-09 : 05:09:50

Hi all
I have my data as follows;

ID - Value - Count - Remarks
----------------------------
1 - s1 - 1 - Proceed
2 - s2 - 2 - No
2 - s2 - 1 - PartiallyAccepted
3 - s1 - 3 - partially
3 - s1 - 2 - OK
4 - s1 - 1 - Accepted

and my output sholud be
ID Value - Count - Remarks
1 - s1 - 1 - Proceed
2 - s2 - 2 - PartiallyAccepted
3 - s1 - 3 - Acceptd

I tried as

select ID,Value,max(Count) as latest,Remarks
from Visits
group by D,Value,Remarks

But i am not getting proper output.
how to pick the corresponding Remarks column.
Thanks.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 05:17:01

quote:
3 - s1 - 3 - Acceptd

how do u get this value?

Dev@nlkss

134 Posts

Posted - 2009-07-09 : 05:21:55

This is direct entry in table.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 05:25:04

[code]SELECT a.ID, a.value, a.count, b.remarks
FROM (
(SELECT ID, value, MAX(Count) as count
FROM Visits
GROUP BY ID, value )a
JOIN
(SELECT *
FROM Visits )b
on a.id=b.id and a.value=b.value and a.countx=b.countx
)[/code]
Hope can help you ^^, but i advise you to wait pros to confirm it.

Dev@nlkss

134 Posts

Posted - 2009-07-09 : 07:16:26

Thanx waterduck,
It worked fine.
ThanQ very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources