Pull Records based on defined hierarchy

I have a table in the following format (columns 1-3)

| 1 |2|3|

|Name_1|A|2|
|Name_1|A|3|
|Name_1|A|4|
|Name_2|B|2|
|Name_2|B|3|
|Name_3|C|1|
|Name_3|C|2|
|Name_3|C|4|

The table is several hundred thousands of rows with records similar to the above scheme.

I'm attempting to pull only one record for each given Name and Letter pair, based on which numbers exist in the table for that pair. (Ex: Name_1 and A should only have one number assigned to it).

Name_1 and A have numbers 1, 2, 3, 4 - where those 4 numbers exist, I would need to use 4 as the record; however, with Name_2 and B, and the numbers 2, 3 (2 may be the record I need). The number that takes precedence depends on the combination of numbers that exists for that Name and Letter pair.

select name, letter
from table
where (name = 'Name_1' and letter = 'A' and record = 4)
OR (name = 'Name_2 and letter = 'B' and record = 2)
group by name, letter
1 Like

@jackiellowery, I appreciate the response. Your solution works great for those two instances.

In the actual table, there are hundreds of thousands of rows, and name/letter pairs, with different number combinations as the third column. The combinations can be different for any unique name/letter pair. I believe I can generate a table to determine which number takes precedence, based on a given combination.

In that case your query would be:

select name, letter
from table tbl
inner join nameletter_record nr
  on tbl.name = nr.name and tbl.letter = nr.letter and tbl.record = nr.record
group by tbl.name, tbl.record
1 Like