April 20, 2010 at 2:53 am
I have a little problem regarding a table. The table consist of a lot of duplicates and non duplicates and I want to sort out the most usal duplicates regarding to flags
Table Information:
ID int
Name nvarchar(50)
Flags nvarchar(50)
So if the list is filled with following
1 Super ABCDE
2 Super ABC
3 Super ABC
4 Super ABC
5 Good ABC
6 Good ABC
7 Sun FLER
8 Sun ERT
9 Good ERT
10 Super ABCDE
the result should be:
3 Super ABC
2 Super ABCDE
1 Good ERT
1 Sun ERT
1 Sun FLER
Any suggestions how to do it? If I do a grouping and count the flags but how do exclude the flags that already exists within the table with a heigher number count than current?
April 20, 2010 at 3:54 am
Always nice to be given the data so we can experiement. A better way of posting the information we need to be able to help is as I've done here 😉
CREATE TABLE [dbo].[test]
(
id INT IDENTITY,
name NVARCHAR(50),
flags NVARCHAR(50)
)
ON [PRIMARY]
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Super',
'ABCDE')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Super',
'ABC')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Super',
'ABC')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Super',
'ABC')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Good',
'ABC')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Good',
'ABC')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Sun',
'FLER')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Sun',
'ERT')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Good',
'ERT')
INSERT INTO [dbo].[test]
(name,
flags)
VALUES ('Super',
'ABCDE')
You'll probably get more responses from that.
-edit- misread the problem so delete my solution.
April 20, 2010 at 5:25 am
I'm not sure I fully understand the question either, but we have to start somewhere, so:
SELECT group_count = COUNT(*),
name,
flags
FROM dbo.test
GROUP BY
name, flags
ORDER BY
COUNT(*) DESC;
Produces:
group_count name flags
3 Super ABC
2 Super ABCDE
2 Good ABC
1 Good ERT
1 Sun ERT
1 Sun FLER
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply