VB SQL Command for Unique Values

[Michael42]

Hello,

In a VB5 DAO application I have a table where a particular field may have duplicate values.

Using VB SQL how can I create a SELECT statement that just returns the unique values for a field?

Thanks,

Michael42

 

[ClulessChris]

Michael,

Try "SELECT DISTINCT feildName "

Everybody body is somebodys Nutter.

 

[Golom]

Try

  Select DISTINCT myField From tbl

 

[DrJavaJoe]

strSQL = "select Distinct Fields from TableName"
Distinct is the keyword that you are looking for.

 

[DrJavaJoe]

OK, you guys are quick.

 

[Michael42]

Quick indeed.

Thanks all!

 

[Golom]

And even more amazing ... we agree!

 

[pullingteeth]

how do i get a COUNT of unique values?

something like

select count (distinct blahblah)

 

[pullingteeth]

Well, I kludged it by doing a resultcount on the RecordSet object returned from my distinct query. Works fine.

 

[MatDavies]

may seem like a small thing, but I think it is worth pointing out. There are different variations of SQL, but VB doesn't really have a SQL. I'm guessing you are probably using MS Access in which you are using Jet SQL. If you wrere using an Oracle Database you would be using oracle SQL.

I just want to point out that the SQL you use in VB depends on what database you are actually using.

(waiting to be shot down in flames.....)

Matt

If you can keep your head while those around you are losing theirs, you obviously haven't grasped the seriousness of the situation

 

[Michael42]

No worries - great point.

Oracle has two types of SQL of course. ANSI and PL. PL being Oracle's proprietary extention. When you take classes at Oracle University they recommend you take the Oracle 9i SQL (or ANSI) then their PL SQL course if you are doing the developer path.

I went the DBA route and just took the standard ANSI type SQL. It covers basic usage but not advanced SQL topics. Oracle claims they are 100% compliant with ANSI SQL.

Yes you are correct that I am using JET\Access and as such ANSI SQL. Sorry I did not mention that up front.

Thanks for the post,

Michael42

 

[rdroske]

And the count thing would have been

Select count(select distinct fieldname from tablename)

It is better, network traffic wise, than selecting the whole recordset and using recordcount.