InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:32:10 GMT20http://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspx[quote][b]mukti.roy (1/12/2012)[/b][hr]I think paul last Query and my Query both are same.[/quote]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'):[code="sql"]SELECT U.* FROM dbo.Users AS UJOIN ( 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[/code]If you're interested in why both query forms produce the execution plans they do, I wrote about that here:[url]http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx[/url]Thu, 12 Jan 2012 07:07:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspxI think paul last Query and my Query both are same. [code="SQL"]Select U.* from dbo.Users U with (Nolock)Join (Select username, MAX(GWP) MGWP from dbo.Users with (Nolock)Group by username) as Aon A.UserName=u.UserName and A.MGWP=U.GWP [/code]suggestions are welcome.Thu, 12 Jan 2012 05:38:38 GMTmukti.royhttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspx[quote][b]apatel 80451 (1/11/2012) via private message[/b][hr]The query works thanks. But I need to include the middle column and this doesnt seem to work in the grouping?[/quote]If you are using SQL Server 2005 or later, you can just add the middle column name to Mark's query suggestion. Otherwise:[code="sql"]CREATE TABLE dbo.Users( UserName varchar(50) NOT NULL, MiddleColumn integer NOT NULL, GWP money NOT NULL);[/code][code="sql"]INSERT dbo.Users (UserName, MiddleColumn, GWP)SELECT 'Salesperson A', 23171, 320230.00 UNION ALLSELECT 'Salesperson B', 11782, 199584.00 UNION ALLSELECT 'Salesperson C', 16769, 14437175.00 UNION ALLSELECT 'Salesperson D', 17191, -635580.00 UNION ALLSELECT 'Salesperson A', 16844, 122000.00 UNION ALLSELECT 'Salesperson B', 18708, 159775.00 UNION ALLSELECT 'Salesperson D', 10969, 225295.00 UNION ALLSELECT 'Salesperson C', 18681, 73040.00 UNION ALLSELECT 'Salesperson D', 26480, 768201.00 UNION ALLSELECT 'Salesperson A', 10310, -111325.00 UNION ALLSELECT 'Salesperson B', 17291, -460550.00 UNION ALLSELECT 'Salesperson B', 11116, 323329.00 UNION ALLSELECT 'Salesperson E', 19162, -65940.00 UNION ALLSELECT 'Salesperson C', 11224, 947025.00 UNION ALLSELECT 'Salesperson A', 10568, 73040.00;[/code][code="sql"]SELECT * FROM dbo.Users AS uWHERE GWP = ( SELECT MAX(u2.GWP) AS MaxGWP FROM dbo.Users AS u2 WHERE u2.UserName = u.UserName )[/code]Wed, 11 Jan 2012 03:34:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspx[quote][b]Mark-101232 (1/11/2012)[/b][hr]Something like this?[code="sql"]WITH CTE AS (SELECT [User],GWP, ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY GWP DESC) AS rnFROM MyTable)SELECT [User],GWPFROM CTEWHERE rn=1;[/code][/quote]Or...[code="sql"]SELECT [User], MAX(GMP)FROM MyTableGROUP BY [User][/code]Wed, 11 Jan 2012 02:49:12 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspxSomething like this?[code="sql"]WITH CTE AS (SELECT [User],GWP, ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY GWP DESC) AS rnFROM MyTable)SELECT [User],GWPFROM CTEWHERE rn=1;[/code]Wed, 11 Jan 2012 02:26:02 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspx[quote][b]apatel 80451 (1/11/2012)[/b][hr]Apologies forget to say how I could group the distinct users and there top GWP?[/quote]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.Wed, 11 Jan 2012 02:19:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspxApologies forget to say how I could group the distinct users and there top GWP?Wed, 11 Jan 2012 02:15:50 GMTSachin 80451http://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspx[quote][b]apatel 80451 (1/11/2012)[/b][hr]I have the following data:UserSalesperson A 23171 320230.00Salesperson B 11782 199584.00Salesperson C 16769 14437175.00Salesperson D 17191 -635580.00Salesperson A 16844 122000.00Salesperson B 18708 159775.00Salesperson D 10969 225295.00Salesperson C 18681 73040.00Salesperson D 26480 768201.00Salesperson A 10310 -111325.00Salesperson B 17291 -460550.00Salesperson B 11116 323329.00Salesperson E 19162 -65940.00Salesperson C 11224 947025.00Salesperson A 10568 73040.00[/quote]Did you have a question?Wed, 11 Jan 2012 02:15:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233790-392-1.aspxI have the following data:UserSalesperson A 23171 320230.00Salesperson B 11782 199584.00Salesperson C 16769 14437175.00Salesperson D 17191 -635580.00Salesperson A 16844 122000.00Salesperson B 18708 159775.00Salesperson D 10969 225295.00Salesperson C 18681 73040.00Salesperson D 26480 768201.00Salesperson A 10310 -111325.00Salesperson B 17291 -460550.00Salesperson B 11116 323329.00Salesperson E 19162 -65940.00Salesperson C 11224 947025.00Salesperson A 10568 73040.00Wed, 11 Jan 2012 02:11:18 GMTSachin 80451