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.
sandya_116Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WizillingCommented:
Hope this helps


SELECT * FROM Table_Name
WHERE (cast(GroupName AS varchar(7)) + cast(OrderCount AS varchar(5))) IN (
SELECT  cast(GroupName AS varchar(7)) + cast(MAX(OrderCount) AS varchar(5)) 
FROM Table_Name
GROUP BY GroupName)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
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

0
Anthony PerkinsCommented:
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
0
Anthony PerkinsCommented:
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
0
Anthony PerkinsCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.