asked on

SQL Query - Distinct with Concatenated Columns

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

ASKER CERTIFIED SOLUTION

Anil Golamari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pra4444

have you tried this

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

Jimbo99999

ASKER

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