We help IT Professionals succeed at work.

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.
Comment
Watch Question

You want to use rank, here is an example http://msdn.microsoft.com/en-us/library/ms176102.aspx
In this example, I use your query as Q1 and add a RN field (using  ROW_NUMBER) to make query Q
Then I select from Q where RN = 1

The ROW_NUMBER call is the imporant bit - the partition by clause says we will reset the row number to 1 whenever the UnitId changes.   Within a UnitId, we order by count desc.


Select Q.* From
(
    Select Q1.*, ROW_NUMBER() OVER (PARTITION BY UnitId Order by [Count] desc) as RN
    From
    (
      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
    ) Q1
) Q
Where RN = 1

Author

Commented:
Thank you both for your comments. I used row_number, not rank, because rank could give me several rows for each partition, which row_number doesn't.