lefodnes
asked on
How to extract the row with the most occuring value in MS SQL?
Consider the following table in MS SQL 2008
CREATE TABLE [dbo].[Unit]([UnitId] [int] NOT NULL) ON [PRIMARY]
CREATE TABLE [dbo].[Prop]([PropId] [int] NOT NULL,[UnitId] [int] NOT NULL,[Letter] [char](1) NOT NULL) ON [PRIMARY]
/****** Object: Table [dbo].[Unit] Script Date: 12/07/2011 12:26:06 ******/
INSERT [dbo].[Unit] ([UnitId]) VALUES (1)
INSERT [dbo].[Unit] ([UnitId]) VALUES (2)
INSERT [dbo].[Unit] ([UnitId]) VALUES (3)
INSERT [dbo].[Unit] ([UnitId]) VALUES (4)
/****** Object: Table [dbo].[Prop] Script Date: 12/07/2011 12:26:06 ******/
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (1, 1, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (2, 2, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (3, 4, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (4, 4, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (5, 4, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (6, 3, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (7, 3, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (8, 2, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (9, 1, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (10, 1, N'A')
Now,
Select u.UnitId, Letter, count(1) as [Count]
from Prop p
inner join Unit u on u.UnitId = p.UnitId
group by u.UnitId, Letter
order by unitid, COUNT(1) desc, letter
gives:
1 A 2 <-
1 C 1
2 B 2 <-
3 A 1 <-
3 C 1
4 A 1 <-
4 B 1
4 C 1
How can I only get
1 A 2
2 B 2
3 A 1 (2 different possible answers, select the first one based on sort)
4 A 1 (3 different possible answers, select the first one based on sort)
? I need to either expand the query or write another one, to be a subquery in my actual "bigger" query
Explanation: I want to know for each UnitId which Letter is occuring the most times within the table Prop, as in the last table. Thank you for any hints.
CREATE TABLE [dbo].[Unit]([UnitId] [int] NOT NULL) ON [PRIMARY]
CREATE TABLE [dbo].[Prop]([PropId] [int] NOT NULL,[UnitId] [int] NOT NULL,[Letter] [char](1) NOT NULL) ON [PRIMARY]
/****** Object: Table [dbo].[Unit] Script Date: 12/07/2011 12:26:06 ******/
INSERT [dbo].[Unit] ([UnitId]) VALUES (1)
INSERT [dbo].[Unit] ([UnitId]) VALUES (2)
INSERT [dbo].[Unit] ([UnitId]) VALUES (3)
INSERT [dbo].[Unit] ([UnitId]) VALUES (4)
/****** Object: Table [dbo].[Prop] Script Date: 12/07/2011 12:26:06 ******/
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (1, 1, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (2, 2, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (3, 4, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (4, 4, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (5, 4, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (6, 3, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (7, 3, N'A')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (8, 2, N'B')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (9, 1, N'C')
INSERT [dbo].[Prop] ([PropId], [UnitId], [Letter]) VALUES (10, 1, N'A')
Now,
Select u.UnitId, Letter, count(1) as [Count]
from Prop p
inner join Unit u on u.UnitId = p.UnitId
group by u.UnitId, Letter
order by unitid, COUNT(1) desc, letter
gives:
1 A 2 <-
1 C 1
2 B 2 <-
3 A 1 <-
3 C 1
4 A 1 <-
4 B 1
4 C 1
How can I only get
1 A 2
2 B 2
3 A 1 (2 different possible answers, select the first one based on sort)
4 A 1 (3 different possible answers, select the first one based on sort)
? I need to either expand the query or write another one, to be a subquery in my actual "bigger" query
Explanation: I want to know for each UnitId which Letter is occuring the most times within the table Prop, as in the last table. Thank you for any hints.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER