[thelist] SQL: Eliminating Duplicates with .getRows()

Joshua Olson joshua at waetech.com
Mon Sep 2 16:24:01 CDT 2002


----- Original Message -----
From: "Ken Kogler" <ken.kogler at curf.edu>
Sent: Monday, September 02, 2002 5:04 PM


> I'm using ASP's .getRows() thingy, but this is more of a SQL question
> than anything else.
>
> I'm trying to eliminate the duplicate records returned from a recordset.
> Here's my situation (stay with me, this gets a bit rough:)

Ken, the solution is going to depend greatly on the database employed.
Using a database that supports subselects, a possible solution would be to
use a query such as this:

SELECT sID, sNumber, sTitle, sOshaAnsi, aApp
FROM tblSigns
WHERE sID IN (
  SELECT sID
  FROM tblSigns, xrefAppsSigns
  WHERE (sTitle LIKE '% s %'
  OR aApp LIKE '% s %')
  AND sNumber = aNumber
)

Everything within the IN clause is your original query, except that the
inner query now only returns sID.

Another option is to use the DISTINCE keyword (which works for databases
that do not support subselects):

SELECT DISTINCT sID, sNumber, sTitle, sOshaAnsi, aApp
FROM tblSigns, xrefAppsSigns
WHERE (sTitle LIKE '% s %'
OR aApp LIKE '% s %')
AND sNumber = aNumber

Another option is to use a group (which works on databases that do not
support subselects or distincts):

SELECT sID, sNumber, sTitle, sOshaAnsi, aApp
FROM tblSigns, xrefAppsSigns
WHERE (sTitle LIKE '% s %'
OR aApp LIKE '% s %')
AND sNumber = aNumber
GROUP BY sID, sNumber, sTitle, sOshaAnsi, aApp

Rudy might suggest that the last option is preferable because you can return
Count(*) to see how many other times that search criteria returned the same
sign.

HTH,

-joshua




More information about the thelist mailing list