INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...Just a quick note to say, "THANKS!" for these forums...The site is very well layed out and easy to use. Thanks for bringing us together - we need each other."
Geography
Where in the world do Tek-Tips members come from?
|
 Majority of Column
|
|
Hi,
I have a table like this:
ColumnA | ColumnB
C 1
C 1
C 1
C 1
C 3
C 2
D 6
D 6
D 6
D 5
I need to write a query that will show only the values that have the majority of the rows. Example C1, D6, but NOT C2,C3 or D5.
This is much tougher than I thought. The number of rows can differ.
Thanks for any help,
Paul |
|
|
TheSQL (Programmer) |
6 Nov 07 23:32 |
How do you define "the majority of rows"?
You could start with
SELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB
ORDER BY Count(*) DESC |
|
I thought someone would have a slick answer to this. This is the only thing I could come up which is a bit convoluted. CODEDECLARE @tv TABLE (
ColumnA char(1),
ColumnB int
)
INSERT INTO @tv
SELECT 'C', 1
UNION ALL
SELECT 'C', 1
UNION ALL
SELECT 'C', 1
UNION ALL
SELECT 'C', 1
UNION ALL
SELECT 'C', 2
UNION ALL
SELECT 'C', 2
UNION ALL
SELECT 'C', 3
UNION ALL
SELECT 'C', 2
UNION ALL
SELECT 'D', 6
UNION ALL
SELECT 'D', 6
UNION ALL
SELECT 'D', 6
UNION ALL
SELECT 'D', 5
UNION ALL
SELECT 'E', 1
UNION ALL
SELECT 'E', 8
UNION ALL
SELECT 'E', 1
UNION ALL
SELECT 'E', 8
/*
The following will also bring back duplicate
ColumnA's i.e. if there are the same number of
ColumnB's per ColumnA - E8 and E1 in the
example data above (2 occurences each).
*/
SELECT t1.ColumnA AS columnA
,t1.ColumnB AS columnB
,t1.myCount AS finalCount
FROM (SELECT COUNT(*) AS myCount,
ColumnA,
ColumnB
FROM @tv
GROUP BY ColumnA,ColumnB ) t1
INNER JOIN
( SELECT MAX( in1Count ) AS myCount
,ColumnA
FROM( SELECT COUNT(*) AS in1Count,
ColumnA,
ColumnB
FROM @tv
GROUP BY ColumnA,ColumnB ) in1
GROUP BY ColumnA ) t2
ON t1.ColumnA = t2.ColumnA
AND t1.myCount = t2.myCount
/*
The following will only bring back unique ColumnA
values.
*/
SELECT t1.ColumnA columnA
,MIN(t1.ColumnB) columnB
,MAX(t1.myCount) finalCount
FROM (SELECT COUNT(*) AS myCount,
ColumnA,
ColumnB
FROM @tv
GROUP BY ColumnA,ColumnB ) t1
INNER JOIN
( SELECT MAX( in1Count ) AS myCount
,ColumnA
FROM( SELECT COUNT(*) AS in1Count,
ColumnA,
ColumnB
FROM @tv
GROUP BY ColumnA,ColumnB ) in1
GROUP BY ColumnA ) t2
ON t1.ColumnA = t2.ColumnA
AND t1.myCount = t2.myCount
GROUP BY t1.ColumnA |
|
It's not pretty but it works CODESELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows into #temp
FROM table
GROUP BY ColumnA, ColumnB
select t.* from #temp t join
(select columna, Max(NbrOfRows) as NBr from #temp group by columna) a
on a.columna = t.columnA and a.nbr = t.NbrOfRows Note this will give you two (or more) rows for a value in columnA if they have the same count. You would need some other way of selecting the one you want in that case if you only want one row. You could do it without the temp table but then you get a derived table inside a derived table and it gets harder to follow. "NOTHING is more important in a database than integrity." ESquared
|
|
|
TheSQL (Programmer) |
7 Nov 07 15:56 |
How about:
--set up the table to test
CREATE TABLE MYTABLE
(ColumnA char(1), ColumnB int)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',3)
INSERT MyTable VALUES ('C',2)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',5)
GO
--actual script
SELECT a.ColumnA, a.ColumnB
FROM (SELECT ColumnA, ColumnB, Count(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB) a
JOIN (SELECT MAX(x.NbrOfRows) AS MaxRows
FROM
(SELECT ColumnA, ColumnB, Count(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB) AS x) as b ON a.NbrOfRows = b.MaxRows |
|
Thankyou to all who went after this brain teaser. Wow, for such a simple thing, it turned into a quite complex query. I did use the version from SQLSister, as it was the shortest and easiest  . SELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows into #temp FROM table GROUP BY ColumnA, ColumnB select t.* from #temp t join (select columna, Max(NbrOfRows) as NBr from #temp group by columna) a on a.columna = t.columnA and a.nbr = t.NbrOfRows |
|
|
 |
|
