SQL question (it should be simple)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL question (it should be simple)

  1. #1
    Join Date
    Jan 2006
    Posts
    5

    Question SQL question (it should be simple)

    I suppose it's a simple question, but I'm a complete newbie with SQL.

    I have a 2-column table with the entries like these:
    John - Smith
    John - Lennon
    Paul - McCartney
    Paul - Mauriat
    Paul - Smith
    Stephen - King
    Stephen - Hawking
    etc...

    I need a table where 1st column contain only unique elements and whatever value in 2nd column, like
    John - Lennon
    Paul - McCartney
    Stephen - King

  2. #2
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by regul8or
    I suppose it's a simple question, but I'm a complete newbie with SQL.

    I have a 2-column table with the entries like these:
    John - Smith
    John - Lennon
    Paul - McCartney
    Paul - Mauriat
    Paul - Smith
    Stephen - King
    Stephen - Hawking
    etc...

    I need a table where 1st column contain only unique elements and whatever value in 2nd column, like
    John - Lennon
    Paul - McCartney
    Stephen - King

    This is one way you could do it:

    SELECT tt1.firstname,
    (select top 1 tt2.lastname from testtable tt2 where tt1.firstname = tt2.firstname) as LastName
    from TestTable tt1
    group by firstname

    Is there any criteria determining which John, Paul, Stephen, etc. would be selected since there are multiples of each?

  3. #3
    Join Date
    Jan 2006
    Posts
    5
    Thanks, Tony

    That's solved it.

  4. #4
    Join Date
    Feb 2006
    Posts
    14
    You can also try with
    select distnct firstname,lastname from tablename;

  5. #5
    Join Date
    Jan 2006
    Posts
    5
    This will return a whole table (all Johns, Pauls etc) and it's clearly not what I needed.

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. SQL Server Insert Question
    By David Satz in forum Database
    Replies: 1
    Last Post: 05-09-2002, 05:09 PM
  3. SQL 2GB+ Memory question
    By Jeremy in forum Database
    Replies: 0
    Last Post: 01-24-2002, 05:13 PM
  4. SQL Question
    By Jim in forum Database
    Replies: 3
    Last Post: 05-19-2000, 03:51 AM
  5. sql simple question about a query
    By Rodolphe Pomerleau in forum Database
    Replies: 1
    Last Post: 04-13-2000, 05:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center