How to fetch related rows on multiple condition with ranking
SQL Query to replicate the data:
CREATE TABLE Products (
  Id INT,
  name NVARCHAR(max),
  category NVARCHAR(max),
  seller INT,
  m_age INT,
  status NVARCHAR(2),
  type_id INT,
  region NVARCHAR(2)
);


INSERT INTO Products
  (Id,
  name,
  category,
  seller,
  m_age,
  status,
  type_id,
  region)
VALUES
('10','TBox','Gun','14','4','Q4','12','EU' ),
('11','Globe','Gun','36','12','Q1','17','EU' ),
('12','Nike','CFK','12','5','Q2','12','AS' ),
('32','Raven','Knife','21','14','Q1','12','EU' ),
('46','TBox','Gun','14','10','Q4','12','AS' ),
('46','Blaster','Gun','14','10','Q4','12','EU' ),
('78','PaintGun','Gun','12','10','Q2','54','US' ),
('98','TBox','OD','21','4','Q3','54','AS');


Solution:
DECLARE @id INT = 10,
@name NVARCHAR(MAX) = 'TBox';

Select
    P4.id,
    P4.name,
    p4.category,
    P4.seller,
    P4.m_age,
    P4.status,
    P4.type_id,
    P4.region,
    (DENSE_RANK() OVER (ORDER BY P3.Category DESC, P3.name DESC, P3.Info DESC, P3.seller DESC)) -1 AS Rank
From (
    Select 
        *,
        ROW_NUMBER() OVER (ORDER BY ID) AS SNO
    From Products
) P4
LEFT JOIN
(Select
    ROW_NUMBER() OVER (ORDER BY P1.ID) AS SNO,
    CASE WHEN P1.Category=P2.Category THEN 1 ELSE 0 END Category,
    CASE WHEN P1.name=P2.name THEN 1 ELSE 0 END name,
    (
        CASE WHEN P1.m_age=P2.m_age THEN 1 ELSE 0 END +
        CASE WHEN P1.status=P2.status THEN 1 ELSE 0 END +
        CASE WHEN P1.type_id=P2.type_id THEN 1 ELSE 0 END +
        CASE WHEN P1.region=P2.region THEN 1 ELSE 0 END
    )                                                       info,
    P1.seller
From Products P1
CROSS Join
(
    Select * From Products
    WHERE Id = @id and name = @name
) P2 ) P3
ON P3.SNO = P4.SNO
ORDER BY P4.ID;

Query Output:
id|name    |category|seller|m_age|status|type_id|region|Rank|
--|--------|--------|------|-----|------|-------|------|----|
10|TBox    |Gun     |    14|    4|Q4    |     12|EU    |   0|
11|Globe   |Gun     |    36|   12|Q1    |     17|EU    |   3|
12|Nike    |CFK     |    12|    5|Q2    |     12|AS    |   7|
32|Raven   |Knife   |    21|   14|Q1    |     12|EU    |   6|
46|TBox    |Gun     |    14|   10|Q4    |     12|AS    |   1|
46|Blaster |Gun     |    14|   10|Q4    |     12|EU    |   2|
78|PaintGun|Gun     |    12|   10|Q2    |     54|US    |   4|
98|TBox    |OD      |    21|    4|Q3    |     54|AS    |   5|

FYI: Rank of Id - 12 and 32 should be flipped as Id 32 record has 2 matched info so it's rank should be higher compared to Id 12 record.