[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Script Help

Posted on 2008-02-04
5
Medium Priority
?
174 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:sandya_116
  • 3
5 Comments
 
LVL 13

Accepted Solution

by:
Wizilling earned 2000 total points
ID: 20819400
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
 
LVL 42

Expert Comment

by:dqmq
ID: 20819601
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20820870
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20820908
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20820912
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question