Finding Duplicate Values in a Column - MapInfo_Pro - 2023

MapInfo Pro Help

Product type
Software
Portfolio
Locate
Product family
MapInfo
Product
MapInfo > MapInfo Pro
Version
2023
Language
English
Product name
MapInfo Pro
Title
MapInfo Pro Help
First publish date
1985
Last updated
2023-09-12
Published on
2023-09-12T16:39:16.995549

Often data is entered into tables by many different users. Sometimes data is repeated, or there is common information in several different records. This section explains how to find all rows in a table that, for a given column, share a value with another row. This is accomplished by performing two SQL Select statements.

The first SQL Select statement produces a query table with two columns. The first column is a list of all unique values in the data column and the second column lists the number of times that each unique value occurs. The second SQL statement compares each data column value with all rows in the Query table where the count is greater than one.

In the next example, there is a table EMPLOYEE that has two columns Id_Num and Name.

To find the duplicate values, perform the two SQL Selects, modifying them where indicated.

  1. On the TABLE tab, in the Selection group, click SQL Select and fill in the SQL Select dialog box.
    • Select Columns: ID_Num, Count(*)
    • from Tables: EMPLOYEE
    • Group by Columns: 1
    • Order by Columns: 2 desc
    • into Table Named: Selection

    Substitute the name of your data column for ID_Num and the name of your table for EMPLOYEE. The number 1 in the Group By Columns box will group the row by ID_Num (the first column). The 2 Desc in the Order by Columns box will arrange the records in descending order based on the values in the count(*) field (the second column).

    This SQL Select statement returns a query table with two columns. The first column contains every identification number possessed by at least one employee. The second column contains the number of employees that have that identification number. The rows are sorted by the number of employees that have each id number (for example, the count).

  2. On the TABLE tab, click SQL Select and fill in the dialog box.

Also, change EMPLOYEE to the name of your table and ID_Num to the name of your data column.

In the example, the SQL statement returns a query table containing all of the rows from EMPLOYEE with duplicated data column values. The where condition selects all rows from EMPLOYEE that have an identification number that is the same as one of the ID numbers in the Count_By_ID query table. This sub-select finds all identification numbers that occur more than once.