Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grouping Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 2:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:23 AM
Points: 50, Visits: 126
I have the following data:
User
Salesperson A 23171 320230.00
Salesperson B 11782 199584.00
Salesperson C 16769 14437175.00
Salesperson D 17191 -635580.00
Salesperson A 16844 122000.00
Salesperson B 18708 159775.00
Salesperson D 10969 225295.00
Salesperson C 18681 73040.00
Salesperson D 26480 768201.00
Salesperson A 10310 -111325.00
Salesperson B 17291 -460550.00
Salesperson B 11116 323329.00
Salesperson E 19162 -65940.00
Salesperson C 11224 947025.00
Salesperson A 10568 73040.00
Post #1233790
Posted Wednesday, January 11, 2012 2:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
apatel 80451 (1/11/2012)
I have the following data:
User
Salesperson A 23171 320230.00
Salesperson B 11782 199584.00
Salesperson C 16769 14437175.00
Salesperson D 17191 -635580.00
Salesperson A 16844 122000.00
Salesperson B 18708 159775.00
Salesperson D 10969 225295.00
Salesperson C 18681 73040.00
Salesperson D 26480 768201.00
Salesperson A 10310 -111325.00
Salesperson B 17291 -460550.00
Salesperson B 11116 323329.00
Salesperson E 19162 -65940.00
Salesperson C 11224 947025.00
Salesperson A 10568 73040.00

Did you have a question?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233795
Posted Wednesday, January 11, 2012 2:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:23 AM
Points: 50, Visits: 126
Apologies forget to say how I could group the distinct users and there top GWP?
Post #1233796
Posted Wednesday, January 11, 2012 2:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
apatel 80451 (1/11/2012)
Apologies forget to say how I could group the distinct users and there top GWP?

Which column is GWP (I have no idea what GWP is by the way)? It would save time here if you could also show the output you are expecting.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233799
Posted Wednesday, January 11, 2012 2:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,682, Visits: 19,601
Something like this?

WITH CTE AS (
SELECT [User],GWP,
ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY GWP DESC) AS rn
FROM MyTable)
SELECT [User],GWP
FROM CTE
WHERE rn=1;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1233803
Posted Wednesday, January 11, 2012 2:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
Mark-101232 (1/11/2012)
Something like this?

WITH CTE AS (
SELECT [User],GWP,
ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY GWP DESC) AS rn
FROM MyTable)
SELECT [User],GWP
FROM CTE
WHERE rn=1;


Or...

SELECT
[User], MAX(GMP)
FROM MyTable
GROUP BY
[User]





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233817
Posted Wednesday, January 11, 2012 3:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
apatel 80451 (1/11/2012) via private message
The query works thanks. But I need to include the middle column and this doesnt seem to work in the grouping?

If you are using SQL Server 2005 or later, you can just add the middle column name to Mark's query suggestion. Otherwise:

CREATE TABLE dbo.Users
(
UserName varchar(50) NOT NULL,
MiddleColumn integer NOT NULL,
GWP money NOT NULL
);

INSERT dbo.Users
(UserName, MiddleColumn, GWP)
SELECT 'Salesperson A', 23171, 320230.00 UNION ALL
SELECT 'Salesperson B', 11782, 199584.00 UNION ALL
SELECT 'Salesperson C', 16769, 14437175.00 UNION ALL
SELECT 'Salesperson D', 17191, -635580.00 UNION ALL
SELECT 'Salesperson A', 16844, 122000.00 UNION ALL
SELECT 'Salesperson B', 18708, 159775.00 UNION ALL
SELECT 'Salesperson D', 10969, 225295.00 UNION ALL
SELECT 'Salesperson C', 18681, 73040.00 UNION ALL
SELECT 'Salesperson D', 26480, 768201.00 UNION ALL
SELECT 'Salesperson A', 10310, -111325.00 UNION ALL
SELECT 'Salesperson B', 17291, -460550.00 UNION ALL
SELECT 'Salesperson B', 11116, 323329.00 UNION ALL
SELECT 'Salesperson E', 19162, -65940.00 UNION ALL
SELECT 'Salesperson C', 11224, 947025.00 UNION ALL
SELECT 'Salesperson A', 10568, 73040.00;

SELECT * 
FROM dbo.Users AS u
WHERE
GWP =
(
SELECT
MAX(u2.GWP) AS MaxGWP
FROM dbo.Users AS u2
WHERE
u2.UserName = u.UserName
)





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233852
Posted Thursday, January 12, 2012 5:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 9, 2013 11:34 PM
Points: 79, Visits: 41
I think paul last Query and my Query both are same.

Select U.* from dbo.Users U with (Nolock)
Join (Select username, MAX(GWP) MGWP from dbo.Users with (Nolock)
Group by username) as A
on A.UserName=u.UserName and A.MGWP=U.GWP

suggestions are welcome.
Post #1234682
Posted Thursday, January 12, 2012 7:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
mukti.roy (1/12/2012)
I think paul last Query and my Query both are same.

They will produce exactly the same results, yes. My comments would be that you should avoid the NOLOCK hint, adopt a clear layout style to help people understand your code, qualify names correctly, use aliases, and pay attention to casing ('username' versus 'UserName'):

SELECT 
U.*
FROM dbo.Users AS U
JOIN
(
SELECT
U2.UserName,
MAX(U2.GWP) AS MGWP
FROM dbo.Users AS U2
GROUP BY
U2.UserName
) AS A ON
A.UserName = U.UserName
AND A.MGWP = U.GWP

If you're interested in why both query forms produce the execution plans they do, I wrote about that here:
http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse