Grouping
-
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
-
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?
-
Apologies forget to say how I could group the distinct users and there top GWP?
-
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.
-
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
-
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]
-
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
)
-
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.
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply