Grouping

Question

Grouping

Sachin 80451

SSCommitted

Points: 1642
More actions
January 11, 2012 at 2:11 am

#391283

I have the following data:
User
Salesperson A23171320230.00
Salesperson B11782199584.00
Salesperson C1676914437175.00
Salesperson D17191-635580.00
Salesperson A16844122000.00
Salesperson B18708159775.00
Salesperson D10969225295.00
Salesperson C1868173040.00
Salesperson D26480768201.00
Salesperson A10310-111325.00
Salesperson B17291-460550.00
Salesperson B11116323329.00
Salesperson E19162-65940.00
Salesperson C11224947025.00
Salesperson A1056873040.00

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

apatel 80451 (1/11/2012)
I have the following data:
User
Salesperson A23171320230.00
Salesperson B11782199584.00
Salesperson C1676914437175.00
Salesperson D17191-635580.00
Salesperson A16844122000.00
Salesperson B18708159775.00
Salesperson D10969225295.00
Salesperson C1868173040.00
Salesperson D26480768201.00
Salesperson A10310-111325.00
Salesperson B17291-460550.00
Salesperson B11116323329.00
Salesperson E19162-65940.00
Salesperson C11224947025.00
Salesperson A1056873040.00

Did you have a question?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sachin 80451 SSCommitted Points: 1642 More actions · Answer 2

Apologies forget to say how I could group the distinct users and there top GWP?

Paul White SSC Guru Points: 150467 More actions · Answer 3

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 4

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;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

Paul White SSC Guru Points: 150467 More actions · Answer 5

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mukti.roy SSC Veteran Points: 239 More actions · Answer 7

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.

Paul White SSC Guru Points: 150467 More actions · Answer 8

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi