How to select the proper rows ? 1

[prinand]

I have a table :
ID = autonumber
username
pcname
logoncount

username + pcname together are unique
but one username can appear several times with various PCnames, and a pc name can appear several times with differenty users and each has a different id number

I want to display every user only once with the Pc if which maxcount is the highest value

ie.
id = 1 username is prinand, pcname = x logoncount = 5
id = 2 username is prinand, pcname = y logoncount = 3
id = 3 username is xx, pcname = ww logoncount = 3
id = 4 username is xx, pcname = uu logoncount = 7

the output should be
id username pcname logoncount:
1 prinand x 5
4 xx uu 7

what works is :
Select username,max(logoncount) from table group by username

but now I still don't know my id and pcname and if I add id, I get an error again

can someone suggest a proper select command ?

 

[gmmastros]

You need to take the query that works and make it a subquery. The outer query will use the results of the subquery to filter the records. Like this...

Select Table.*
From   Table
       Inner Join (
            Select username,
                   max(logoncount) AS MaxLogonCount
            from   table 
            group by username
            ) As A On  Table.username = A.username
                   And table.logoncount = a.maxlogoncount

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You could join your table to the query that gives result you want?

select a.ID, a.username, a.pcname, a.logoncount
from table a
inner join 
(Select username,max(logoncount) as MLC from table group by username) b
on a.username = b.username and a.logoncount = b.MLC

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

SELECT UserName, PCName, LogCount
       FROM MyTable
INNER JOIN (SELECT UserName, MAX(LogCount) AS LogCount
                    FROM MyTable
                    GROUP BY UserName) Tbl1
ON MyTable.UserName = Tbl1.UserName AND
   MyTable.LogCount = Tbl1.LogCount

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

hehe at least not outside the five minute rule

A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

3 for 3. Except for the aliases, the queries are the same. Looks like we beat this one to death.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

George, your arsenal of smileys never ceases to amaze me

A wise man once said
"The only thing normal about database guys is their tables".

 

[prinand]

Hey AlexCuse thanks a lot ! (and the rest too for the quick responses)

I did not know you could define a select as a "kind of" temp table called b as you did
I was already looking at creating and dropping a table but this is much easier


thanks all, this solved my issues !



an idiot can ask more questions than 10 wise men can answer