asked on

Script Help

I have a script that shows GroupName, LoginID, and OrderCount ordered by Groupname, OrderCount in descending order. There are only 3 groups. Example data is as follows:
Group1 abc 24
Group1 def 20
Group1 ghi 18
Group1 jkl 10
Group2 abc2 30
Group2 def2 24
Group2 ghi2 15
Group2 jkl2 8
Group3 abc3 14
Group3 def3 12
Group3 ghi3 10
Group3 jkl3 5

I need to pull which LoginID in each group has the highest OrderCount? so there will be total of 3 rows. Can someone let me know how I can do that? Thanks.

ASKER CERTIFIED SOLUTION

Wizilling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

Use the first example, if you do not care about ties. Use the second example if you want to eliminate ties.

select T1.* from YourTable T1
where T1.OrderCount =  
(select max(T2.OrderCount) from yourtable T2 where t1.groupname = t2.groupname)
 
 
select T1.* from YourTable T1
where T1.OrderCount =  
(select max(T2.OrderCount) from yourtable T2 where t1.groupname = t2.groupname)
and 
(select count(*) from YourTable T3 where
t3.ordercount =
(select max(T4.OrderCount) from yourtable T4 where t3.groupname = t4.groupname)
and T3.loginID < T1.LoginID
and T3.GroupName = T1.GroupName) = 0

Open in new window

Anthony Perkins

Try it this way:

Select      t.GroupName,
      t.LongID,
      t.OrderCount
From      YourTableName t
      Inner Join (
            Select      GroupName,
                  MAX(OrderCount) MaxOrderCount
            From      YourTableName
            Group By
                  GroupName) d On t.GroupName = d.GroupName And t.OrderCount = d.MaxOrderCount

Anthony Perkins

A slight typo and correction:

Select      t.GroupName,
      t.LoginID,
      t.OrderCount
From      YourTableName t
      Inner Join (
            Select      GroupName,
                  MAX(OrderCount) MaxOrderCount
            From      YourTableName
            Group By
                  GroupName) d On t.GroupName = d.GroupName And t.OrderCount = d.MaxOrderCount
Order By
      t.GroupName

Anthony Perkins

Incidentally this is how I tested it:

Create Table YourTableName (
            GroupName varchar(50),
            LoginID varchar(10),
            OrderCount integer)

SET NOCOUNT ON
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group1', 'abc', 24)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group1', 'def', 20)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group1', 'ghi', 18)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group1', 'jkl', 10)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group2', 'abc2', 30)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group2', 'def2', 24)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group2', 'ghi2', 15)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group2', 'jkl2', 8)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group3', 'abc3', 14)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group3', 'def3', 12)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group3', 'ghi3', 10)
Insert      YourTableName (GroupName, LoginID, OrderCount) Values ('Group3', 'jkl3', 5)

Select      t.GroupName,
      t.LoginID,
      t.OrderCount
From      YourTableName t
      Inner Join (
            Select      GroupName,
                  MAX(OrderCount) MaxOrderCount
            From      YourTableName
            Group By
                  GroupName) d On t.GroupName = d.GroupName And t.OrderCount = d.MaxOrderCount
Order By
      t.GroupName

Drop Table YourTableName

And here is my output:
GroupName      LoginID      OrderCount
Group1      abc      24
Group2      abc2      30
Group3      abc3      14