How to fetch related rows on multiple condition with ranking

Question

I have following table for storing item information in PostgreSQL.

id  |  name      | category | seller | m_age | status | type_id | region |
--------------------------------------------------------------------------
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   |

I want to find related items on basic of certain criteria.

Category > title > Tech Info Matched(m_age, status, type_id, region) > Seller

So for id 10 - TBox item rank should be following(name and category column value and Info matched and reason cololms are for visual reference only)

id  | name  | category | INFO MATCHED | RANK |             REASON              |
--------------------------------------------------------------------------------
10  | TBox  |  Gun     |      -       |   -  |                -                |
--------------------------------------------------------------------------------
11  | Diff  |  Same    |      1       |   3  | same cat diff name 1 info match |
12  | Diff  |  Diff    |      1       |   6  | diff cat diff name 1 info match |
32  | Diff  |  Diff    |      2       |   7  | diff cat diff name 2 info match |
46  | Same  |  Same    |      3       |   1  | same cat and name               |
46  | Diff  |  Same    |      4       |   2  | same cat diff name 4 info match |
78  | Diff  |  Same    |      0       |   4  | same cat diff name 0 info match |
98  | Same  |  Diff    |      1       |   5  | diff cat same name              |

I am still a novice to SQL. I have tried with normal CASE and function. but haven't got proper results. Rank will not work with this many condition. Is there any way to pull this results anyhow with PostgreSQL?

Answer 1

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.