In MapInfo, we try to create a SQL Select query to show the segments
that have a count greater than 1. The query I create looks like this:
Select columns: START_X, START_Y, END_X, END_Y, count(*)
from tables: Streets
Group by columns: START_X, START_Y, END_X, END_Y
But I am unsure how to get the query to return just rows where the
count(*) is greater than 1. I have tried aliases the count(*) field
but it does not like my syntax.
Then once I have this query working in Map Info, I have tried to take
the query and use it in Map Basic. This will allow the users to run
the command from a tool bar without having to enter the information
every time. But the query always just returns 1 row with the total
count. Here is the query I am running there:
Run Command "Select START_X, END_X, START_Y, END_Y, count(*) from
STREETS ORDER BY START_X, END_X, START_Y, END_Y into qryDups"
Shouldn't this query work the same in Map Basic as it does in Map
Info? Has anyone had experience with trying to return counts in this
manner?
Eventually I want to show the street names, zips and counts for
segments that have more than one street name associated with them.
Thanks in advance for any help with this.
Tina
Select START_X, END_X, START_Y, END_Y, count(*)"Ct" from STREETS into
qryGroups noselect
Select START_X, END_X, START_Y, END_Y, Ct from qryGroups where ct > 1 into
qryDups NoSelect ORDER BY START_X, END_X, START_Y, END_Y
Aliasing may or not work depending on your version of MapInfo; if it
doesn't, you'll have to use the name _COL5 in the second query instead of
the alias "Ct" I gave the count aggregate column.
Hope this helps
Spencer
Select START_X, END_X, START_Y, END_Y, count(*)"Ct" from STREETS into
qryGroups noselect group by START_X, END_X, START_Y, END_Y
Second question. Is there a way to select FullName from the Streets
table for the duplicate records?
With this query, I will need to join in the Streets table to display
the FullName field.
Select START_X, END_X, START_Y, END_Y, Ct from qryGroups where ct
> 1 into
qryDups NoSelect ORDER BY START_X, END_X, START_Y, END_Y
Thanks for your help on this!!
On Mar 16, 12:18 pm, "Spencer Simpson" <ssimp...@baltometro.org>
wrote: