How do I select distinct on one column and return all other columns?

Question

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

        cn.Open()
        Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
        dt.Load(rdr)

        SelectField.ShowDialog()


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

        dBaseConnection.Open()

        Dim SQLCreateCommand As String

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

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

        dBaseCommand.ExecuteNonQuery()
        dBaseConnection.Close()

        cn.Close()
        cn.Dispose()
    End If**

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

Answer 1

Some SQL databases let you do something like:

SELECT
  DISTINCT ON(Version)
  FirstName, LastName
  /* INTO NewTable */
FROM
  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:

SELECT
    Version, first(FirstName) AS FirstName, first(LastName) AS LastName
FROM
    (SELECT 
       * 
    FROM 
         TableName
    ORDER BY
          Version  /* and whichever columns make sense for your purpose */
    ) AS OrderedTable
GROUP BY
    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.

Answer 2

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