How do I SQL Select distinct on field into new table, while keeping all columns?

[chrisetiquette]

Hoping this is the right forum! Im having trouble with this and have tried and looked everywhere. I need to write a SQL Statement in vb.net/Access that will select one of each version (version being based of a field called code) and then output all the columns with one of each version, like this:

The trick is I need it to output all columns variably, meaning that that the names and amount of columns in my database will change often, but the code field will always be the same.

Input:

Code First Last
A Chris Smith
A Tod Spinach
B Alex Toller
C Jen Havert
C Burt Reynolds




Output:
Code First Last
A Chris Smith
B Alex Toller
C Jen Havert

Here is the line that is giving me trouble:

"SELECT DISTINCT " & CODE & " 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 " & CODE & " 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 works in SQL but I guess RowNumber and Partition is not supported by vb.net or access :

select *
from (
  select *,
         row_number() over (partition by Code order by Code) as rn
  from TableName
) t
where rn = 1
order by Code

[aytee111]

This being an Access forum... You will need to use the Grouping/Totals method in the query - Group By your field Code and take the First of everything else. Create a query in Access to see how this works then copy the SQL to your code.

[chrisetiquette]

Thank you, but I am posting because I do not know the exact syntax on how to carry that out. I have tried many things with no success. So, could you provide an example please?

[aytee111]

Create a query in Access, click on the Totals icon at the top.

[InsuranceGuy]

You're correct that Access does not support Row_Number.


If I wanted the first edition of any books I own where I have potentially multiple editions, I would write two queries in Access:


FirstOwnedEditions: Select [Book_ID], min([Edition_Number]) My_First_Edition from Table_Books group by [Book_ID]
DesiredResultSet: Select Table_Books.* from Table_Books inner join FirstOwnedEditions on Table_Books.[Book_ID] = FirstOwnedEditions.[Book_ID] and Table_Books.[Edition_Number]=FirstOwnedEditions.[My_First_Edition]