Selecting nth top row based on number of occurrences of value in 3 tables
I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that count?
Example:
TableA.D1
1
2
1
1
3
TableB.D2
2
1
1
1
2
3
TableC.D3
2
1
3
So the output for 3rd most common value would look like this:
3 -- number 3 appeared only 3 times
Likewise, output for 2nd most common value would be:
2 -- number 2 appeared 4 times
And output for 1st most common value:
1 -- number 1 appeared 7 times
sql
sql-server sql-rank edited Nov 21 '18 at 18:42
Salman A
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
add a comment |
I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that count?
Example:
TableA.D1
1
2
1
1
3
TableB.D2
2
1
1
1
2
3
TableC.D3
2
1
3
So the output for 3rd most common value would look like this:
3 -- number 3 appeared only 3 times
Likewise, output for 2nd most common value would be:
2 -- number 2 appeared 4 times
And output for 1st most common value:
1 -- number 1 appeared 7 times
sql
sql-server sql-rank edited Nov 21 '18 at 18:42
Salman A
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
add a comment |
I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that count?
Example:
TableA.D1
1
2
1
1
3
TableB.D2
2
1
1
1
2
3
TableC.D3
2
1
3
So the output for 3rd most common value would look like this:
3 -- number 3 appeared only 3 times
Likewise, output for 2nd most common value would be:
2 -- number 2 appeared 4 times
And output for 1st most common value:
1 -- number 1 appeared 7 times
sql
sql-server sql-rank edited Nov 21 '18 at 18:42
Salman A
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that count?
Example:
TableA.D1
1
2
1
1
3
TableB.D2
2
1
1
1
2
3
TableC.D3
2
1
3
So the output for 3rd most common value would look like this:
3 -- number 3 appeared only 3 times
Likewise, output for 2nd most common value would be:
2 -- number 2 appeared 4 times
And output for 1st most common value:
1 -- number 1 appeared 7 times
sql
sql-server sql-rank sql
sql-server sql-rank edited Nov 21 '18 at 18:42
Salman A
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
edited Nov 21 '18 at 18:42
Salman A
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
edited Nov 21 '18 at 18:42
Salman A
177k66337425
edited Nov 21 '18 at 18:42
Salman A
177k66337425
edited Nov 21 '18 at 18:42
Salman A
177k66337425
177k66337425
asked Nov 21 '18 at 15:57
adammoadammo
215
asked Nov 21 '18 at 15:57
adammoadammo
215
asked Nov 21 '18 at 15:57
adammoadammo
215
215
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
You probably want :
select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
add a comment |
SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3 --the third in the order of commonness - note that 'ties' can be handled differently
answered Nov 21 '18 at 16:12
CatoCato
2,714210
add a comment |
Use UNION ALL to combine rows from all tables into one, group them to count, and use DENSE_RANK() to find nth-row:
WITH cte1 AS (
SELECT d1 FROM tablea UNION ALL
SELECT d2 FROM tableb UNION ALL
SELECT d3 FROM tablec
), cte2 AS (
SELECT d1, COUNT(*) AS c
FROM cte1
GROUP BY d1
), cte3 AS (
SELECT d1, c, DENSE_RANK() OVER (ORDER BY c DESC) AS rnk
FROM cte2
)
SELECT *
FROM cte3
WHERE rnk = 3
DB Fiddle
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
add a comment |
One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:
declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc
... it's extremely readable, and doesn't use any concepts that are tough to grok.
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415910%2fselecting-nth-top-row-based-on-number-of-occurrences-of-value-in-3-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
You probably want :
select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
add a comment |
You probably want :
select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
add a comment |
You probably want :
select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
You probably want :
select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
edited Nov 21 '18 at 16:10
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
answered Nov 21 '18 at 16:05
Yogesh SharmaYogesh Sharma
29.1k51336
29.1k51336
add a comment |
add a comment |
SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3 --the third in the order of commonness - note that 'ties' can be handled differently
answered Nov 21 '18 at 16:12
CatoCato
2,714210
add a comment |
SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3 --the third in the order of commonness - note that 'ties' can be handled differently
answered Nov 21 '18 at 16:12
CatoCato
2,714210
add a comment |
SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3 --the third in the order of commonness - note that 'ties' can be handled differently
answered Nov 21 '18 at 16:12
CatoCato
2,714210
SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3 --the third in the order of commonness - note that 'ties' can be handled differently
answered Nov 21 '18 at 16:12
CatoCato
2,714210
answered Nov 21 '18 at 16:12
CatoCato
2,714210
answered Nov 21 '18 at 16:12
CatoCato
2,714210
answered Nov 21 '18 at 16:12
CatoCato
2,714210
2,714210
add a comment |
add a comment |
Use UNION ALL to combine rows from all tables into one, group them to count, and use DENSE_RANK() to find nth-row:
WITH cte1 AS (
SELECT d1 FROM tablea UNION ALL
SELECT d2 FROM tableb UNION ALL
SELECT d3 FROM tablec
), cte2 AS (
SELECT d1, COUNT(*) AS c
FROM cte1
GROUP BY d1
), cte3 AS (
SELECT d1, c, DENSE_RANK() OVER (ORDER BY c DESC) AS rnk
FROM cte2
)
SELECT *
FROM cte3
WHERE rnk = 3
DB Fiddle
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
add a comment |
Use UNION ALL to combine rows from all tables into one, group them to count, and use DENSE_RANK() to find nth-row:
WITH cte1 AS (
SELECT d1 FROM tablea UNION ALL
SELECT d2 FROM tableb UNION ALL
SELECT d3 FROM tablec
), cte2 AS (
SELECT d1, COUNT(*) AS c
FROM cte1
GROUP BY d1
), cte3 AS (
SELECT d1, c, DENSE_RANK() OVER (ORDER BY c DESC) AS rnk
FROM cte2
)
SELECT *
FROM cte3
WHERE rnk = 3
DB Fiddle
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
add a comment |
Use UNION ALL to combine rows from all tables into one, group them to count, and use DENSE_RANK() to find nth-row:
WITH cte1 AS (
SELECT d1 FROM tablea UNION ALL
SELECT d2 FROM tableb UNION ALL
SELECT d3 FROM tablec
), cte2 AS (
SELECT d1, COUNT(*) AS c
FROM cte1
GROUP BY d1
), cte3 AS (
SELECT d1, c, DENSE_RANK() OVER (ORDER BY c DESC) AS rnk
FROM cte2
)
SELECT *
FROM cte3
WHERE rnk = 3
DB Fiddle
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
Use UNION ALL to combine rows from all tables into one, group them to count, and use DENSE_RANK() to find nth-row:
WITH cte1 AS (
SELECT d1 FROM tablea UNION ALL
SELECT d2 FROM tableb UNION ALL
SELECT d3 FROM tablec
), cte2 AS (
SELECT d1, COUNT(*) AS c
FROM cte1
GROUP BY d1
), cte3 AS (
SELECT d1, c, DENSE_RANK() OVER (ORDER BY c DESC) AS rnk
FROM cte2
)
SELECT *
FROM cte3
WHERE rnk = 3
DB Fiddle
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
edited Nov 21 '18 at 16:16
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
answered Nov 21 '18 at 16:08
Salman ASalman A
177k66337425
177k66337425
add a comment |
add a comment |
One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:
declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc
... it's extremely readable, and doesn't use any concepts that are tough to grok.
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
add a comment |
One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:
declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc
... it's extremely readable, and doesn't use any concepts that are tough to grok.
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
add a comment |
One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:
declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc
... it's extremely readable, and doesn't use any concepts that are tough to grok.
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:
declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc
... it's extremely readable, and doesn't use any concepts that are tough to grok.
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
answered Nov 21 '18 at 16:40
KevinKevin
1,624418
1,624418
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415910%2fselecting-nth-top-row-based-on-number-of-occurrences-of-value-in-3-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
