How do I SELECT DISTINCT on a field into a new table while including all columns?

Question

How do I SELECT DISTINCT on a field into a new table while including all columns?

chriso 73655

SSC Journeyman

Points: 78
More actions
December 8, 2016 at 11:05 am

#401184

I am doing this in vb.net but I cannot get the proper syntax down for the SQL Statement. I need to export one record of each version to a new table, while including all columns.
This is my statement I am using, but it does not include all the columns just the field i need to select one of each unique value:
"SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName
Here is the rest 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

row_number featuring partition by will do what you are asking;

here's an old example, but you can see you get all the other values but it's logically grouped by the partition column (the chk_no in the example)

select Row_number() over (PARTITION BY X.CHK_NO ORDER BY X.CHK_NO),X.*

From (

SELECT 1234 AS chk_no,'medc' AS ben_code UNION

SELECT 1234,'ss' UNION

SELECT 1234,'fica' UNION

SELECT 1234,'hlth' UNION

SELECT 1235,'medc' UNION

SELECT 1235,'ss' UNION

SELECT 1235,'fica' UNION

SELECT 1235,'hlth' UNION

SELECT 1235,'dent' UNION

SELECT 1236,'medc' UNION

SELECT 1236,'ss' UNION

SELECT 1236,'fica' UNION

SELECT 1236,'hlth' UNION

SELECT 1237,'medc' UNION

SELECT 1237,'ss' UNION

SELECT 1237,'fica' UNION

SELECT 1237,'hlth' UNION

SELECT 1237,'dent' UNION

SELECT 1237,'csup') X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chriso 73655 SSC Journeyman Points: 78 More actions · Answer 2

Can you please format it into something more relevant/shorter? I am very new to SQL and do not quite understand your syntax, please see my code.

doug.brown SSCertifiable Points: 5607 More actions · Answer 3

The first line of Lowell's example provides what you've asked for; the distinct values from one column in a table X, while selecting all the values from the other columns in X. Here it is using your pseudo-code names:

SELECT Row_number() OVER(PARTITION BY TableName.Field1 ORDER BY TableName.Field1 ), TableName.*

You won't need the row number itself in the results; it's just there to get the distinct values.

chriso 73655 SSC Journeyman Points: 78 More actions · Answer 4

I got it to work in SQL but I figured out that it's not a SQL issue this line works:

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

This issue is I am trying to run this sql statement through vb.net, and vb.net does not support row number() or partition....

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

chriso 73655 (12/9/2016)
I got it to work in SQL but I figured out that it's not a SQL issue this line works:
select * Into NewTabe from( select *, row_number()over(partition by Code order by Code) As rn From database) t where rn = 1 order by Code
This issue is I am trying to run this sql statement through vb.net, and vb.net does not support row number() or partition....

VB.NET doesn't support the ROW_NUMBER SQL function because it's a SQL function. You're not firing your SQL statement in VB.NET, but from VB.NET. It executes against a database and SQL Server does support the ROW_NUMBER function.

What Lowell posted will return everything, but only the first occurrence of each Code. I'd suggest you get it working in SSMS, then make it into a stored procedure, then call the procedure with your .NET application.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions