I am sure this is so simple but I am stumped
-
Please bear with me, I can't think around this problem and really need your help.
CREATE TABLE [dbo].[ATable]([ID] [int] NOT NULL,
[peter] [int] NULL,
[mike] [int] NULL,
[steve] [int] NULL,
CONSTRAINT [PK_ATable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ATable] ([ID], [peter], [mike], [steve]) VALUES (1, 1, 2, 22)
INSERT [dbo].[ATable] ([ID], [peter], [mike], [steve]) VALUES (2, 1, 1, 33)
INSERT [dbo].[ATable] ([ID], [peter], [mike], [steve]) VALUES (3, 2, 2, 25)
INSERT [dbo].[ATable] ([ID], [peter], [mike], [steve]) VALUES (4, 2, 1, 59)
INSERT [dbo].[ATable] ([ID], [peter], [mike], [steve]) VALUES (5, 3, 1, 44)
With this table I would like to return for every unique value in Peter the max value for Mike AND include the data in steve thats in the same row as mike.
For example
I would like to return:
Peter Mike Steve
1 2 22
2 2 25
3 1 44
I have tried:
SELECT Peter, max(mike) AS mike, steveFROM ATable
group by peter, steve
But this will return all the results because steve is included in the group by clause.
I am sure this is really simple but I am lost. I would really appreciate your help, forgive my stupidity.
Many Thanks,
Oliver
-
with cte1 as
(
SELECT
ROW_NUMBER() over(partition by peter order by peter) as row_num,
Peter, max(mike) AS mike, steve
FROM ATable
group by peter, steve
)
select * from cte1 where row_num = 1
might be worth reading up on the ranking functions, row_number/rank/dense rank/ntile etc
-
WITH CTE AS (SELECT ID, peter, mike, steve,
ROW_NUMBER() OVER(PARTITION BY Peter ORDER BY Mike DESC) AS rn
FROM dbo.ATable)
SELECT peter, mike, steve
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
-
Thank you so much, I really appreciate the help. Will definately read up on partition by and ranking functions. Sometimes just finding what the relevant functions are called is half the battle.
Once again, thank you very much,
Oliver
-
Hi,
This is a different solution, just to sum up with the good ones you already have.
select distinct a.Peter, m.Mike,m.Steve from ATable a
cross apply(select top 1 Mike,steve,peter from atable where peter = a.peter order BY mike desc) m
-
much appreciated, will give this one a go as well.
Many Thanks,
Oliver
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply