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

[Christhemist]

I need to select one of each version in the data while keeping all the columns as well. Then I need to export the one record of each version to a new .dbf file. The issue I am having is with the SQL statement. I have tried different iterations of the SQL Statement but nothing appears to work. The code I have provided selects one of each version but does not include the rest of the columns. Here is 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

[digitalShaman]

this seems to be more a sort of SQL question. the thing is you say "but does not include the rest of the columns". What do you expect to see there? if there are two rows with identical values in Field1, but different values in Field2, how should your result look like?

[Christhemist]

I wouldn't say its so much a SQL question because I've tested out the following code in sql and it works perfectly, however I am getting this error now:

Syntax error (missing operator) in query expression 'row_number()over(partition by Code order by Code)'.

Here is the code that works in sql but gives the above error when I run in in vb.net:

Dim sql2 = "select * Into NewTabe from( select *, row_number()over(partition by Code order by Code) As rn From TableName) t where rn = 1 order by Code"

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

[Christhemist]

The issue I've found is that vb.net does not support "Row Number()" and "Partition" so if anyone has any suggestions on how to recreate this I am all ears!

[si_the_geek]

I'm afraid that is nothing to do with VB.Net, because all VB is doing is passing a String to the database engine... VB doesn't even know if it is an SQL statement or not.

What you need to work out is what syntax Jet (dBase IV) provides for this kind of thing.


I personally don't know, but have moved this thread to our 'Database Development' forum (which is where you should always post SQL questions, as SQL is certainly not specific to VB), hopefully somebody here will be able to help.

[Christhemist]

This is what ended up working. I put all my fields into a string called "Fields"

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

[FunkyDexter]

You were getting the syntax error because Row_Number is a SQL Server function and you're using Access.

As for how to get distinct values while keeping all column, you need to think a bit about what that means. If you mean, "I want to select the distinct combinations across all columns then that's quite easy. Either:-

Insert Into NewTable
Select Distinct *
From ExistingtTable

or

Insert Into NewTable
Select ListAllYourColumns
From ExistingTable
Group By ListAllYourColumns

If you mean that you want to select all the distinct values from a subset of columns while retaining the values from the other columns - you can't do that because it doesn't actually make sense. For a given combination of the distinct columns, you might have more than one value in the other columns - what would you do then?