Detect if larger value is not in place (1 Viewer)

jysharp2003

New member
Local time
Today, 03:39
Joined
Feb 19, 2018
Messages
9
Hi,
I have a customer application that needs to detect and query return records that do not have the largest value in field. Example below:
field value is 1.1.1.1 where in another table there is a value of 1.1.1.1.1 that should be in it's place.
I think that maybe a query that detects period count greater than 3 (in this example) and having the same numeric of one's (1.1.1.1) that would return in query would be our need.
Another example to solidify the need is:
field value is 1.13.1.6 where in another table there is a value of 1.13.1.6.8 that should be in its place.
Logic should be if value contains 3 periods which equates to 4 segments then the query should detect and alert on "same" numeric 4 segment values and return which is the MAX 5th (or higher).
Example in screenshot shows a 5th segment available value that should be in the U Temp record.
1.13.1.13 inside story should be 1.13.1.13.8 inside story from tv which is the MAX. I have uploaded a DB to share the sample tables.
I can't say how much I appreciate your time in getting the query right and accurate for detecting availables.
 

Attachments

  • screenshot of Max logic.jpg
    screenshot of Max logic.jpg
    180.9 KB · Views: 19
  • chkPeriodCounts.zip
    74.5 KB · Views: 15

CJ_London

Super Moderator
Staff member
Local time
Today, 09:39
Joined
Feb 19, 2013
Messages
12,599
Somewhat confused by the description
this says everything but the largest
I have a customer application that needs to detect and query return records that do not have the largest value in field.

and this says the largest
Example below:
field value is 1.1.1.1 where in another table there is a value of 1.1.1.1.1 that should be in it's place.

Assuming the latter would have though using a non standard join would work - perhaps something like this to find the max in the other table.
Code:
SELECT left(A.uid, instrrev(A.uid,".")) as grouping, max(B.uid) as maxValue
From myTable A INNER JOIN myOtherTable B ON left(A.uid, instrrev(A.uid,"."))=left(B.uid, instrrev(B.uid,"."))
GROUP BY  left(A.uid, instrrev(A.uid,"."))
this can then be joined to myTable in another query to compare and see which has the highest value.

This is just a pointer for a direction you could go, I don't want to get involved with the detail
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:39
Joined
Feb 19, 2002
Messages
30,132
I'm not sure why the same data is being stored in multiple places. That is certainly contributing to your problems. The other thing is that strings separated by dots will NOT ever sort or compare "logically" UNLESS you make the stanzas fixed in length such as:
0001.0001.00001.0013.0001.0004

A better solution if the depth is fixed is to just have each stanza as a separate NUMERIC field. The best solution is probably to normalize and use a recursive table which is very difficult for most people to manage.
 

Users who are viewing this thread

Top Bottom