0

Given this data:

Bolt_Table:

PID UNIQ ID GROUP_ID Distance
PID_24_2225 14 13 1141
PID_5_1444E 3214 13 652
PID_5_14454 3152 13 802
PID_24_2225 15 14 1141
PID_5_14454 3151 14 802
PID_5_1444E 3213 14 652
PID_26_21FC 536 2300 597
PID_5_13388 4121 2300 620
PID_5_13382 4169 2300 802

This is the desired result:

PID UNIQ_ID GROUP_ID Distance
PID_5_1444E 3214 13 652
PID_5_1444E 3213 14 652
PID_5_13388 4121 2300 620

Explanation:

  • 1st Record: @Group ID = 13,

    Get the similar PID

    PID_5_1444E and PID_5_14454 - compare the corresponding distances minimum of (652 and 802). Since 652 is the least, the corresponding PID: " PID_5_1444E " should be retained, hence record 1 of the desired table

What would be the query for SQL? (Microsoft Access)

I tried using LIKE, MID(String,1,4), GROUP BY & HAVING but nothing seems to work. How should I make the query for this?

The closest one I got is when I force to do the judging under a hard coded GROUP_ID,I would like to do it FOR EACH GROUP_ID

SELECT TOP 1 PERCENT PID, UNIQ_ID, GROUP_ID, Distance
FROM
(
SELECT
a.PID, a.UNIQ_ID, a.GROUP_ID, ID, a.Distance,
(select count(PID) as counter from Bolt_Table where GROUP_ID = a.GROUP_ID and LEFT(PID, 9) = LEFT(a.PID, 9)) as counter from Bolt_Table a WHERE a.GROUP_ID = 13
)
where counter > 1 
order by Distance
Jose Jaime Felix Garcia
  • 1
  • 2
  • 2
    Hey Just selecting the lowest distance record doesn't per group doesn't work for you?Identifying and calculating similar rows is tricky in SQL. – Equinox Feb 07 '23 at 08:30
  • this is the code to get lowest distance record for each group `SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY GROUP_ID ORDER BY DISTANCE) AS RN FROM TABLE1 ) XX WHERE RN = 1` – Equinox Feb 07 '23 at 08:38
  • Access does not support Row_Number(). – June7 Feb 07 '23 at 08:45
  • 1
    Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) – June7 Feb 07 '23 at 08:46
  • I was able to figure out similar PIDs. I have posted the code in the description. The only problem now is how to make that code work for every bolt ID – Jose Jaime Felix Garcia Feb 08 '23 at 07:51

1 Answers1

0

SELECT b.pid, b.[uniq id], b.group_id, b.distance FROM bolt_table as b INNER JOIN (SELECT group_id, min(distance) as mindist FROM bolt_table GROUP BY group_id) as a on b.group_id = a.group_id AND b.distance = a.mindist

Capnbigal
  • 27
  • 5
  • Close, what you did was to get the minimum per Group_ID, but we have to take note that the requirement is to get what is only "Similar" within that group ID. Take a look at the record WHERE PID=PID_26_21FC, it is the minimum in the group but it is not in the desired table since only those who are similar should be compared. In that Group (Group_ID=2300), only PID_5_13388 and PID_5_13382 should be compared. – Jose Jaime Felix Garcia Feb 08 '23 at 00:47