• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

GROUP BY MySQL query question

I have a businesses database with duplicates. When i GROUP BY BusinessName in a City always the first result in the DB will appear. Example:

ID 1 - BusinessName A (old result with old address, phone ..)
ID 2 - BusinessName A (new result with new address, phone ..)

In this case ID 1 will be shown.

Now when I import a new business database with new businesses I would like to echo ID 2 instead of ID 1 because ID 2 has updated address, phone number and so on. How can this be done without removing ID 1 from DB?

The best solution would be to replace ID 1 with ID 2. The problem is each business has a unique ID which is indexed in search engines therefore removing the old ID 1 would hurt search engine traffic.

Thanks for some input!
0
seopti
Asked:
seopti
1 Solution
 
GaryCommented:
Why don't you reverse order the results based on the ID (I assume is incremental) so the newest is always first?

If you are just wanting the latest record, but selecting multiple companies in the same query then you would need a sub query, something like this

SELECT * FROM table
    WHERE  id IN (select max(id)
    from table
    group by company_name)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Ray PaseurCommented:
You might try an order clause like ORDER BY ID DESC.

Another option might be to add a column of type = TIMESTAMP.  MySQL will update this column with the current date / time value whenever the row changes.  So if you ORDER BY timestamp_column DESC you will always find the most recent value, even if the ID values change.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now