Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping


Grouping

Author
Message
Sachin 80451
Sachin 80451
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Sachin 80451
Sachin 80451
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 126
Apologies forget to say how I could group the distinct users and there top GWP?
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22803
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
mukti.roy
mukti.roy
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search