I'd like to make a modification to this SELECT statement so that it only returns a single result in an array of matches; namely, the lowest Damerau-Levenshtein value that it finds. Currently I'm getting multiple pairs presented depending on how high I set the threshold in the WHERE clause (currently <6): - SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, TICO.TICO_lName, TICO.TICO_fName, TICO.TICO_Lic, TICO.TICO_SupLic, TICO.TICO_Notes, DamerauLevenshtein(TCSDBOWNER_EMP.LAST_NAME & TCSDBOWNER_EMP.FIRST_NAME,TICO.[TICO_lName] & TICO.[TICO_fName]) AS DamLev
-
FROM TICO, TCSDBOWNER_EMP
-
WHERE (((DamerauLevenshtein([TCSDBOWNER_EMP].[LAST_NAME] & [TCSDBOWNER_EMP].[FIRST_NAME],[TICO].[TICO_lName] & [TICO].[TICO_fName]))<6))
-
ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME;
will result in: - EMP_ID EMP_LNAME EMP_FNAME EMP_EMAIL ACTIVE TICO_lName TICO_fName TICO_Lic TICO_SupLic TICO_Notes DamLev
-
111111 REYNOLD MONA x T REYNOLD SANA T123456 x x 2
-
111111 REYNOLD MONA x T REYNOLD MONA T654321 x x 0
-
222222 REYNOLD SANA x T REYNOLD MONA T654321 x x 2
-
222222 REYNOLD SANA x T REYNOLD SANA T123456 x x 0
That didn't line up very well... the numbers on the far right are the Damereau-Levenshtein results (DamLev). "0" is a perfect match, where "2" means it's 2 characters off. I want "<6" to be an acceptable match if there are no better matches available, but otherwise it should be discarded.
I'm pretty sure that it would involve using an array for the results and then just picking the lowest result, but I'm not sure how to implement that in the SQL. I see other threads here about arrays in SQL so I believe it's possible, but I'm not sure how to just isolate the matches and not create an array of the entire table of records.
Thanks for your thoughts...
Oh and the tables share no relationship. TCSDBOWNER_EMP.ID is a primary key and there's no key in TICO.
Something like this should work. - SELECT t1.*
-
-
FROM (
-
levenshtein query
-
) AS t1
-
-
INNER JOIN (
-
SELECT IDField, MIN(LevDistance) AS MinLevDistance
-
FROM (levenshtein query) AS x
-
GROUP BY IDField
-
) AS t2
-
-
ON t1.IDField = t2.IDField AND
-
t1.LevDistance = t2.MinLevDistance
13 1209
I just tried using this function but it bogged down the computer ridiculously: - Function MinOfList(ParamArray varValues()) As Variant
-
Dim i As Integer 'Loop controller.
-
Dim varMin As Variant 'Smallest value found so far.
-
-
varMin = Null 'Initialize to null
-
-
For i = LBound(varValues) To UBound(varValues)
-
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
-
If varMin <= varValues(i) Then
-
'do nothing
-
Else
-
varMin = varValues(i)
-
End If
-
End If
-
Next
-
-
MinOfList = varMin
-
End Function
Thinking now MIN() might be the answer but I get a message about EMP_ID not being an aggregate something....?
applied here: - SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, TICO.TICO_lName, TICO.TICO_fName, TICO.TICO_Lic, TICO.TICO_SupLic, TICO.TICO_Notes, MIN(DamerauLevenshtein(TCSDBOWNER_EMP.LAST_NAME & TCSDBOWNER_EMP.FIRST_NAME,TICO.[TICO_lName] & TICO.[TICO_fName])) AS DamLev
-
FROM TICO, TCSDBOWNER_EMP
-
WHERE (((DamerauLevenshtein([TCSDBOWNER_EMP].[LAST_NAME] & [TCSDBOWNER_EMP].[FIRST_NAME],[TICO].[TICO_lName] & [TICO].[TICO_fName]))<6))
-
ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME;
Something like this should work. - SELECT t1.*
-
-
FROM (
-
levenshtein query
-
) AS t1
-
-
INNER JOIN (
-
SELECT IDField, MIN(LevDistance) AS MinLevDistance
-
FROM (levenshtein query) AS x
-
GROUP BY IDField
-
) AS t2
-
-
ON t1.IDField = t2.IDField AND
-
t1.LevDistance = t2.MinLevDistance
NeoPa 32,556
Expert Mod 16PB
Hi Jen.
I assume it's the lowest value of [DamLev] you're after, but matched up with the data from your other tables?
BTW.
To match columns within the [CODE] tags just make sure to use all spaces and no TABs ;-)
Also, to show a [CODE] block without an extraneous line at the end simply put the [/CODE] tag at the end of the last line and not on a new one.
webbeacon but I get a message about EMP_ID not being an aggregate something....?
Just an aside... 'Aggregate Functions' (SUM, MIN, MAX, AVG, etc) are calculations that span multiple rows in your query. The GROUP BY clause defines the range of records that these functions span - for example a SUM per employee, etc. The error message you received, in plain English means that for these types of queries, ALL fields selected must either be in Aggregate Functions or in a GROUP BY clause.
NeoPa:
I assume it's the lowest value of [DamLev] you're after
Yes that's right
but matched up with the data from your other tables?
DamLev has already performed a match, and the number represents the quality of the match.
And thank you for cleaning up my table :)
mbizup:
The error message you received, in plain English means that for these types of queries, ALL fields selected must either be in Aggregate Functions or in a GROUP BY clause.
[strike]So does that mean that if I use a GROUP BY at the end of the statement, without any other changes, it would probably work?[/strike]
This did not work. LOL
Rabbit: This works beautifully, thank you very much! Here's the adapted version of your code: - SELECT t1.*
-
FROM (
-
-
SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, TICO.TICO_lName, TICO.TICO_fName, TICO.TICO_Lic, TICO.TICO_SupLic, TICO.TICO_Notes, DamerauLevenshtein(TCSDBOWNER_EMP.LAST_NAME & TCSDBOWNER_EMP.FIRST_NAME,TICO.[TICO_lName] & TICO.[TICO_fName]) AS DamLev FROM TICO, TCSDBOWNER_EMP WHERE DamerauLevenshtein([TCSDBOWNER_EMP].[LAST_NAME] & [TCSDBOWNER_EMP].[FIRST_NAME],[TICO].[TICO_lName] & [TICO].[TICO_fName])<3 AND TCSDBOWNER_EMP.ACTIVE_FLAG="T" ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME
-
-
) AS t1
-
-
INNER JOIN (
-
-
SELECT EMP_ID, MIN(DamLev) AS MinDamLev FROM (
-
-
SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, TICO.TICO_lName, TICO.TICO_fName, TICO.TICO_Lic, TICO.TICO_SupLic, TICO.TICO_Notes, DamerauLevenshtein(TCSDBOWNER_EMP.LAST_NAME & TCSDBOWNER_EMP.FIRST_NAME,TICO.[TICO_lName] & TICO.[TICO_fName]) AS DamLev FROM TICO, TCSDBOWNER_EMP WHERE DamerauLevenshtein([TCSDBOWNER_EMP].[LAST_NAME] & [TCSDBOWNER_EMP].[FIRST_NAME],[TICO].[TICO_lName] & [TICO].[TICO_fName])<3 AND TCSDBOWNER_EMP.ACTIVE_FLAG="T" ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME
-
-
) AS x
-
-
GROUP BY EMP_ID
-
-
) AS t2 ON (t1.EMP_ID = t2.EMP_ID) AND (t1.DamLev = t2.MinDamLev);
I have one follow-up question for you: I see the 2nd Levenshtein query is being pulled "AS x"... what's being done with x? I don't see it being called anywhere. Is that just a holder so it can be grouped and passed to t2?
Subqueries need a name. X is the name I gave the subquery.
NeoPa 32,556
Expert Mod 16PB WebBeacon:
I see the 2nd Levenshtein query is being pulled "AS x"... what's being done with x? I don't see it being called anywhere. Is that just a holder so it can be grouped and passed to t2?
Essentially, yes it is just a holder or marker in this case. It's probably not necessary here. However, many people find it easier to work with subqueries when every one is ALIASed ( AS is an alias for ALIAS , and both are optional. IE. FROM (X) AS Y could equally be written FROM (X) Y or FROM (X) ALIAS Y ).
NeoPa 32,556
Expert Mod 16PB Rabbit:
Subqueries need a name. X is the name I gave the subquery.
Sorry Rabbit. We cross-posted.
Although my testing showed that this isn't absolutely necessary, at least in Access, I would say it may well be a requirement in SQL Server, and maybe even in the SQL standard. It's probably a good idea to follow this practice in all circumstances anyway. I know I do. I meant to include that in my earlier response.
You guys are great. Thank you so much :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: George Dainis |
last post by:
How do I code a SQL SELECT statement so that always only this record is retrieved
which matches a certain criteria AND has the lowest ID (= value in key field aaa)?
It must me something like
...
|
by: Terentius Neo |
last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a
select statement? I mean something like this:
Select
c.number,
call procedure( c.number ) as list
from
table c
With best...
|
by: Polly |
last post by:
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to...
|
by: SFM |
last post by:
I just want a simple datareader, that i can read the value returned
from a select statement executed on a SQL server 2005 db.
The code below should work in, but email= rdr.ToString();
when i...
|
by: =?Utf-8?B?VGhvbWFzVDIy?= |
last post by:
Hello,
I have an array that holds 4 values ( they contain random numbers). I would
like to find the lowest value (if there is a tie i would like to find one of
the tie.) then remove that value....
|
by: davenet |
last post by:
Hi,
I'm new to Python and working on a school assignment.
I have setup a dictionary where the keys point to an object. Each
object has two member variables. I need to find the smallest value...
|
by: sebouh181 |
last post by:
Hi all,
I have a XML file like this format
<global>
<Item Key="Key1" value="Value1"/>
<Item Key="Key2" value="Value2"/>
<Item Key="Key3" value="Value3"/>
.
.
|
by: micky125 |
last post by:
Hi all, i've been pondering with an idea for my system to allow the user to make reports depending on what he needs. His input screen will be a series of select options corresponding to the table...
|
by: ankitmathur |
last post by:
Hi All,
I'm facing a problem I'm unable to find a solution for. I hope fellow members would be able to help me out.
System Info:
PHP v5
MSSQL 2008
Linux box
|
by: kirubagari |
last post by:
My form displays a combo box populated by a SQL SELECT statement. The user
will select a row from the combo box. I then want the value in a field of the
row selected in the combo box to be used...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |