1

I have a table, as follows:

ID   |  Name     |  MyID
1    |  Alan     |  2
2    |  Alan     |  2
3    |  John     |  3

I want to select the records as follows:

  1. If two records have the duplicate values in ‘Name’ column, then only the first record is selected and the other record is discarded, even if the two records have different values in other columns.
  2. If two records have different ‘Name’ value, then they are ordered via ‘MyID’ column in ascendant order.
  3. All columns of the records should be returned.

So for the above records, the query should select the following ones:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

How to write the query? It seems I should use DISTINCT or GROUP BY, but my queries are all fail. Please help.

Thanks

  • "then only the first record is selected". Please define first. The one with smaller id ? And please add the DBMS you use. This kind of queries may be solved in different ways, not all available in every DBMS. – ypercubeᵀᴹ Feb 4 '16 at 7:29
  • Can a name have different myid and still be identical? – Julien Vavasseur Feb 4 '16 at 7:39
1

This query with a DISTINCT:

SELECT DISTINCT Name, MyID
FROM data;

Returns distinct Name and MyID:

Name     |  MyID
Alan     |  2
John     |  3

This query with a GROUP BY:

SELECT MIN(ID) AS ID, Name, MyID
FROM data
GROUP BY Name, MyID;

Returns Name and MyId with the smallest ID like your sample:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

Both query are in this SQL Fiddle and will work with most RDBMS. (Sample uses SQL Server)

1

I'm guessing this is what you're after.

SELECT MIN(ID) AS ID,
       Name, MyID
FROM tablename
GROUP BY MyID, Name;

If Name can differ between two MyID, it gets a bit more complicated.

SELECT ID, Name, MyID
FROM (
    SELECT ID, Name, MyID,
          ROW_NUMBER() OVER (PARTITION BY MyID ORDER BY ID) AS _rn
    FROM tablename
    ) AS sub
WHERE _rn=1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.