is ther wa way to join two tables by table2 having rows with the lonest posible match for rows in table1? e.g. say you have data "a, ab, abc, abcd" and you want abcde to match against the lonegst match, in this case abcd, and no other one


simple example, imagine a table with two columns, firstName and lastName ... there could be several rows with firstName identical, but with different lastNames, I want to select all the firstNames with each firstName having the longest lastName associated with it from all the like firstNames

I want to select every unique first field such that the second field would have the longest value in that group

basically if there are more than 1 row with the same first field, I want it to return only one of those lines, but specifically the one with the longest second field

I have no clue how to even write this select statement, the only thing I can think of is to order it by length and then group it, but in sql you have to group before ordering, hence I am stuck