0

I have this data:

From this:

TDNO        location

12345       New York
12345       San Jose
56789       San Francisco
56789       New Jersey
90390       Las Vegas

To this:

TDNO        Location

12345       New York
56789       San Francisco
90390       Las Vegas

How do I achieve this result? If there are duplicate data in TDNO column, only the first data in each duplicate is shown like in the example above.

Rak
  • 139
  • 5
  • 20
  • Maybe this answer will be helpful: https://stackoverflow.com/questions/5021693/distinct-for-only-one-column – Kerkouch May 07 '18 at 01:56
  • `SELECT * FROM table_name GROUP BY TDNO;` Example: http://www.sqlfiddle.com/#!9/cc4db0/4 – Kerkouch May 07 '18 at 01:57
  • Since you are asking about grouping rows by `TDNO` but only returning a specific row from each set, you could probably benefit from reading some of the questions tagged [greatest-n-per-group](https://stackoverflow.com/questions/tagged/greatest-n-per-group) and a bit more on [group-wise maximum](https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html) – But those new buttons though.. May 07 '18 at 03:07

2 Answers2

2

SQL tables represent unordered sets. There is no such thing as a "first" row, unless a column contains that information. You don't specify such a column in the question, but if you have one, a query would look like this:

select t.*
from t
where t.created_at = (select min(t2.created_at) from t t2 where t2.tdno = t.tdno);

I made up the column name created_at for illustration purposes.

If you don't have such a column, perhaps you can recreate the table with an auto_increment column, so you do have a unique column that specifies the ordering.

The next best thing you can do is use an aggregation query:

select tdno, min(location)
from t
group by tdno;

This returns the first value alphabetically. That is quite different from the "first" value (for most definitions of "first"), but it does return one row per tdno.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
  1. Make the column a unique key.
  2. Use ON DUPLICATE UPDATE when inserting.
user207421
  • 305,947
  • 44
  • 307
  • 483
  • I think he wants duplicates rows removed only on select. – Kerkouch May 07 '18 at 01:55
  • No I don't want to update it, I want to have it duplicate because I need the other duplicate to something else so I don't want to update it. – Rak May 07 '18 at 01:56
  • @Karkouch yes I only want it to be removed in select query – Rak May 07 '18 at 01:57
  • 1
    @Rak: this might be mentioned in the actual question, that this data is in a MySQL table, and that you are running a SELECT query that is returning a result, and you desire a different result. The question entirely omits these important details. The question says "mysql - load only one data" +10. – spencer7593 May 07 '18 at 02:00
  • @Karkouch The question has 'load' in the title, and SELECT isn't mentioned anywhere. OP needs to clarify his question, but this answer is reasonable given what was provided. – user207421 May 07 '18 at 02:16