I am using vb.net/access/Microsoft.Jet.OLEDB.4.0/DbaseIV to do this. The syntax varies between vb.net and SQl Server so I am having trouble.

I need to select one of each version in the data (using the code field) while keeping all the columns in the final output. I am selecting distinct on a code field but I cannot figure how to return the rest of the columns.

The issue is with this line

Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

Here is all of my code:

**Dim ofd As New OpenFileDialog
    With ofd
        .Filter = "DBASE File (*.dbf)|*.dbf"
        .Multiselect = False
        .CheckFileExists = True
    End With

    If ofd.ShowDialog() = DialogResult.OK Then
        Dim fi As New IO.FileInfo(ofd.FileName)
        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" _
                                            & fi.DirectoryName & "'")
        Dim TableName As String = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length)
        Dim cmd As New OleDb.OleDbCommand(TableName, cn)
        cmd.CommandType = CommandType.TableDirect

        Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader


        Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)


        Dim SQLCreateCommand As String

        Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

        Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql2, dBaseConnection)


    End If**

This is just an example of my output i need to select all columns using an asterisk or something....

  • Does the table has any other columns? Any UNIQUE or PRIMARY KEY constraint? It would help others to answer better if you added the CREATE TABLE statement. Dec 10 '16 at 17:21

Some SQL databases let you do something like:

  DISTINCT ON(Version)
  FirstName, LastName
  /* INTO NewTable */
  Table ;

This "DISTINCT ON(x)" is implemented only by a limited number of databases. I don't think it is implemented by "Access", and I would think that most probably is not implemented by the "dBase IV" interface.

The SQL used by Access (don't know if the dBase IV variant will work) might let you do something equivalent:

    Version, first(FirstName) AS FirstName, first(LastName) AS LastName
          Version  /* and whichever columns make sense for your purpose */
    ) AS OrderedTable
    Version ;

First is an aggregate function, that, together with GROUP BY, will make sure that you get one value for each group. This value will be the "first one found for the group", so you need to order first your table in a sensible way.

If none of these forms work... My guess is that you will have to solve the problem outside of SQL, getting a RecordSet into your program, looping checking, and inserting into NewTable.

  • @ypercubeᵀᴹ: I stand corrected
    – joanolo
    Dec 10 '16 at 17:28

This is what I ended up using. I concatenated all my fields into "Fields":

insert into NewTable select " & Fields & " FROM (Select * from " & TableName & " order by " & Version & " )group by " & Version

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.