SELECT DISTINCT FROM SQL SERVER USING C# & ADO.NET

[orekinbck]

Hi There

Our test database has duplicate data:

COMPANYID COMPANYNAME
1 Grupple Group
2 Grupple Group
5 Grupple Group
3 Yada Inc
4 Yada Inc
6 Bodgy Brothers

I want to get a list of distinct company names and the most recent ID
for each distinct company name - For example, the query should return:

5 Grupple Group
4 Yada Inc
6 Bodgy Brothers

I tried
SELECT DISTINCT COMPANYNAME, ID FROM MYTABLE ORDER BY COMPANYNAME
But the above query does not work as I expect.

How do I form an SQL query that gets back the DataSet I am after? I
would prefer not to use stored procedures.

TIA
Bill

[Marinus Holkema]

Distinct makes all te slected fields distinct. So
SELECT DISTINCT CompanyName
FROM MyTable

Gives you all distinct company names

If you want an id with it you have to do something like this:

SELECT Max(companyID), CompanyName
FROM MyTable
GROUP BY CompanyName

And there is no word C# in it!

"or*******@yahoo.com.au" wrote:

Hi There

Our test database has duplicate data:

COMPANYID COMPANYNAME
1 Grupple Group
2 Grupple Group
5 Grupple Group
3 Yada Inc
4 Yada Inc
6 Bodgy Brothers

I want to get a list of distinct company names and the most recent ID
for each distinct company name - For example, the query should return:

5 Grupple Group
4 Yada Inc
6 Bodgy Brothers

I tried
SELECT DISTINCT COMPANYNAME, ID FROM MYTABLE ORDER BY COMPANYNAME
But the above query does not work as I expect.

How do I form an SQL query that gets back the DataSet I am after? I
would prefer not to use stored procedures.

TIA
Bill

[Jon Skeet [C# MVP]]

<or*******@yahoo.com.au> wrote:

Our test database has duplicate data:

COMPANYID COMPANYNAME
1 Grupple Group
2 Grupple Group
5 Grupple Group
3 Yada Inc
4 Yada Inc
6 Bodgy Brothers

I want to get a list of distinct company names and the most recent ID
for each distinct company name - For example, the query should return:

5 Grupple Group
4 Yada Inc
6 Bodgy Brothers

I tried
SELECT DISTINCT COMPANYNAME, ID FROM MYTABLE ORDER BY COMPANYNAME
But the above query does not work as I expect.

Firstly, it would help if you'd say what it *does* do.

Then get it to work in SQL Query Analyzer. If you have problems with
that, ask on a SQL group - this isn't really a C# question.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[orekinbck]

Thanks Marinus, that worked perfectly