SQL Query  - Distinct with Concatenated Columns

Jimbo99999
Jimbo99999 used Ask the Experts™
on
Good Day Experts!

I am in need of assistance with a SQL query for use in my VB.net application.  I am getting first name and last name from seperate columns in a table. Then I had to concatenate the two seperated by a comma to show Last, First in my combo box.  

The trouble lies in the SQL table...there are at times 2 records with the same first and last name as a result of DataEntry duplication.  So, I am seeing 2 rows of Smith, Bob in my combobox.  

How can I get only one to be returned back in my query results? I tried looking at Distinct but could not get it to work.  

Here is my query that works but shows duplicates:

Select lname + ',' + fname as name from Login where corporation like '%Contech%'
                                        order by name asc

Thanks,
jimbo99999
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select a.lname + ',' + a.fname as name
from (
Select distinct  lname , fname
from Login where corporation like '%Contech%'
order by 1, 2 asc
) a

try this query

Commented:
have you tried this

Select distinct(lname + ',' + fname) as name from Login where corporation like '%Contech%'  order by  distinct(lname + ',' + fname)

Author

Commented:
It has an issue with the order by 1,2 but I was able to get the following version to work.  Surprisingly it was ordered properly.

SELECT     lname + ',' + fname AS name
FROM         (SELECT DISTINCT lname, fname
                       FROM          Login
                       WHERE      corporation LIKE '%Contech%') a

Thanks,
jimbo99999

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial