help - 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 2005 Forums

Transact-SQL (2005)

help

Author

Topic

zez0
Starting Member

24 Posts

Posted - 2008-06-22 : 07:27:25

SELECT MAX(counter) AS Expr1, qusId, empName
FROM empCounter AS empCounter_1
GROUP BY qusId

this query is wrong becuse empName dosn't in the group by clusev
but
i want to display only the empName of the maxmum counter for each qusId
how can i do this

zezo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-22 : 10:27:44

SELECT empName, counter
FROM (
SELECT empName, counter, ROW_NUMBER() OVER (PARTITION BY qusID ORDER BY counter DESC) AS RecID
FROM empCounter
) AS f
WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:53:04

SELECT e.qusId,b.empName,b.counter
FROM empcounter e
CROSS APPLY (SELECT TOP 1 empName,counter
             FROM empcounter
             WHERE qusId=e.qusId
             ORDER BY counter DESC)b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources