Small Sql Statement Brain Teaser

I’ve just remembered a very small yet challenging SQL statement brain teaser a friend of mine asked me long ago.





Though as simple as it might seem, I still couldn’t figure out how to solve it until this day. I just thought that some of you guys might have an answer to it.





Here is the question:





You have a table named “table1� that has only 2 fields: “name� and “phone�.





This table is populated with the following sample data





NamePhone


---------------------


John111111


Tom222222


Jack333333


John444444


Smith555555


----------------------


5 records








To get all the distinct names in this table, you would write the sql statement:


Select distinct name from table1





And the result would be:





Name


-------


John


Tom


Jack


Smith


-------


4 records





The question is: can you write a sql statement that can return all the distinct names along with their phone numbers.





E.g.





NamePhone


---------------------


John111111


Tom222222


Jack333333


Smith555555


----------------------


4 Records