Return row with most matching parameters
I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:
+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
| 1 | 36 | 1 | 4 |
| 2 | 36 | null | 2 |
| 3 | 36 | 4 | null |
+-------+------------+------------+-----------+
And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.
Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.
If there is too little information about this problem, or it's not clear, feel free to ask for more detail.
sql-server
asked Feb 6 at 10:55
FiNFiN
1745
add a comment |
I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:
+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
| 1 | 36 | 1 | 4 |
| 2 | 36 | null | 2 |
| 3 | 36 | 4 | null |
+-------+------------+------------+-----------+
And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.
Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.
If there is too little information about this problem, or it's not clear, feel free to ask for more detail.
sql-server
asked Feb 6 at 10:55
FiNFiN
1745
add a comment |
I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:
+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
| 1 | 36 | 1 | 4 |
| 2 | 36 | null | 2 |
| 3 | 36 | 4 | null |
+-------+------------+------------+-----------+
And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.
Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.
If there is too little information about this problem, or it's not clear, feel free to ask for more detail.
sql-server
asked Feb 6 at 10:55
FiNFiN
1745
I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:
+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
| 1 | 36 | 1 | 4 |
| 2 | 36 | null | 2 |
| 3 | 36 | 4 | null |
+-------+------------+------------+-----------+
And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.
Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.
If there is too little information about this problem, or it's not clear, feel free to ask for more detail.
sql-server
sql-server
asked Feb 6 at 10:55
FiNFiN
1745
asked Feb 6 at 10:55
FiNFiN
1745
asked Feb 6 at 10:55
FiNFiN
1745
asked Feb 6 at 10:55
FiNFiN
1745
asked Feb 6 at 10:55
FiNFiN
1745
1745
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
;WITH c AS
(
SELECT RowID,
c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
+ CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
+ CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
add a comment |
Try something like this - but see if you can understand the concept.
SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f229012%2freturn-row-with-most-matching-parameters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
;WITH c AS
(
SELECT RowID,
c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
+ CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
+ CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
add a comment |
;WITH c AS
(
SELECT RowID,
c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
+ CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
+ CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
add a comment |
;WITH c AS
(
SELECT RowID,
c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
+ CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
+ CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
;WITH c AS
(
SELECT RowID,
c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
+ CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
+ CASE WHEN CompanyID = @CompanyID THEN 1 ELSE 0 END
FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
answered Feb 6 at 11:59
Aaron Bertrand♦Aaron Bertrand
151k18288488
151k18288488
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
add a comment |
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
2
2
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Additionally, changing the substituted value (instead of 1) in the CASE statement will allow sorting based on different weights for the fields.
– Akina
Feb 6 at 12:15
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
Elegant and readable, just the way I like it. Well done.
– FiN
Feb 8 at 7:51
add a comment |
Try something like this - but see if you can understand the concept.
SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
add a comment |
Try something like this - but see if you can understand the concept.
SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
add a comment |
Try something like this - but see if you can understand the concept.
SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
Try something like this - but see if you can understand the concept.
SELECT TOP (1) RowID
FROM
(
SELECT RowID
FROM YourTable
WHERE DocumentID = @DocumentID
UNION ALL
SELECT RowID
FROM YourTable
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT RowID
FROM YourTable
WHERE CompanyID = @CompanyID
) r
GROUP BY RowID
ORDER BY COUNT(*) DESC, RowID ASC;
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
answered Feb 6 at 11:53
Rob FarleyRob Farley
13.9k12549
13.9k12549
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f229012%2freturn-row-with-most-matching-parameters%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
