By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 17 '05 #2

P: n/a
<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
Nov 17 '05 #3

P: n/a
Thanks Marinus, that worked perfectly

Nov 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.