24903

Copy two columns from one table to another, but only unique values

Question:

I have table1, which has

MBID | Artist __________________ 123321 The Beatles 123214 Led Zeppelin 123321 The Beatles

How can I copy all of the <strong>distinct</strong> MBID's along with their corresponding Artist name, into a new table, so that the new table only has distinct MBID's

MBID | Artist __________________ 123321 The Beatles 123214 Led Zeppelin

I've tried

insert into table2 (MBID,artist) select distinct(table1.MBID),table1.artist FROM danktable

But this gives me weird combinations and not only distinct MBID's

When I make the MBID a primary index, I get an error with this query because i'm getting non-unique MBID values.

Could someone help me out?

Thanks !

Answer1:

You can do it as follows :

insert into table2 (MBID,artist) select MBID,max(artist) from table1 group by MBID

Recommend

  • Remove annotation while keeping plot matplotlib
  • How to perform a left join in SQLALchemy?
  • Foreign key pointing to Compound Key
  • sql1 to get names and sql2 to get Impressions, I need to sort names by Impressions
  • have mysql select statement return fully qualified column names like table.field
  • find xml element by attribute
  • Add delivery info to query in SAP Crystal Reports
  • Migrating row from one table to another with conditions [MYSQL]
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Query to get the Top 2 from each group
  • How to implement limit with Nhibernate and Sybase
  • Insert audio album in Android MediaStore
  • d3.js selection conditional rendering
  • Use default value of a column in stored procedures
  • INSERT EXEC Statement cannot be nested [duplicate]
  • Returning the auto incrementing value after an insert using slick
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • Most efficient way to move table rows from one table to another
  • How can Delete be both a DDL and a DML statement
  • In matplotlib, how do you change the fontsize of a single figure?
  • Enabling DTD support in Sql Server
  • SAXReader not re-ecape characters
  • Adding elements to a huge XML file
  • close() was never explicitly called on database
  • Eliminate partial duplicate rows from result set
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • Transactional Create with Validation in ServiceStack Redis Client
  • Functions in global context
  • Row Count Is Returning the incorrect number using RaptureXML
  • PHP - How to update data to MySQL when click a radio button
  • Submit form in a displaytag pagination
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Delete MySQLi record without showing the id in the URL
  • XCode can't find symbols for a specific iOS library/framework project
  • Unanticipated behavior
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Trying to get generic when generic is not available