Sort without displaying Duplicates

G

Guest

Hi everybody,
I like to asked anyone who is very good at sql to help me display this
data in a table format that duplicate headings, subheadings or other columns
won't appear twice or more. Can anyone help me? I need to sort them and load
them in a Datatable. Loop thorugh all records and display them in a label
control created at run time. Thanks in advance.

Sample Data:

Topics Table
TopicId TopicLevel Topic ParentId
1 1 First 0
2 1 AnotherFirst 0
3 2 Second 1
4 2 AnotherSecond 2
5 3 Third 3

Contents Table
ContentID Heading SubHeading SubSubHeading1 NumberText Names Content1
ParentId
1 How are you? Hello Someone 1 Ben some
text 5
2 How are you? Hello Someone 1 John other text 5
3 How are you? Hello Anybody 2 Ben some
text 5
4 How are you? Hello Anybody 2 Mike other
text 5
5 How are you? Greet Anywhere 1 Ben some
text 5
6 How are you? Greet Anywhere 1 Luke some
text 5
7 I miss you. Really When null null some
text 5
8 I miss you. Really When null null some
text 5

Display Format:
How are you?
----------------------------------------------
Hello
-----------------------------------------------
1 Someone
----------------------------------------------
Ben some text
---------------------------------------------
John other text
---------------------------------------------
2 Anybody
---------------------------------------------
Ben some text
---------------------------------------------
Mike other text
--------------------------------------------
Greet
-------------------------------------------------
1 Anywhere
-----------------------------------
Ben some text
-------------------------------------
Luke some text
================================================
I miss you
 
C

Cor Ligthert [MVP]

Bill,

The OP was asking SQL, therefore I gave not our sample from the distinct
method with the dataview sort and the table.

However this message is more to show you this method which is in my opinion
very hidden on MSDN. (I knew that it exist and needed than some time to find
it)

http://msdn2.microsoft.com/en-us/library/h2b6ehaa.aspx

That other sample on MSDN and our website (much shorter) becomes than
outdated

Cor
 
W

W.G. Ryan - MVP

You lost me buddy. If he wants to do it on the server, wouldn't select
distinct work? Or, he could chose to do it client side although I realize
that's not what he originally asked. My thinking on this is that unless
the sorts change, it's probably best to sort it on the server and just fill
the datatable with the data already in place.
 
G

Guest

Thanks both of you, WG Ryan and Cor Ligthert for replies.
I am confused never heard or knew about this approach. Select Distinct in
Dataset and DataView.ToTable, are all this found in .Net Framework 2.0, I am
using Visual Studio 2003, (.Net Framework Ver 1.1). Can both of you explain
exactly what it does, the database and appllication who will used the data
(sorted) will be on same computer.
Basically, the data is stored like shown with same heading, subheading,
which I do not want to be displayed repeatedly. If these solutions you guys
provided is only applicable in Ver 2.0, Is there another way to do them in
Ver 1.1? Thanks very much. Hope to hear from you guys soon.

den2005
 
G

Guest

Hi Cor and W G
By the way, I forgot to ask when I view the links you guys provided. The
document shows it creates the columns for the data object, what if I used
another data object say a DataTable and used this same or copy it to another
data object (Dataset or DataTable) and applied the Methods (Select Distinct
or DataView.ToTable) will it still works. That is if I could be able to used
Ver 2.0.
Thanks.

den2005
 
W

W.G. Ryan - MVP

Den - I apologize but I don't follow you on this. Can you explain it a
little more. Thx
 
W

W.G. Ryan - MVP

You can do it in either version, it's just easier in 2.0.
When you do a select distinct, either on the server or client side, you will
only get one instance of that field. There are some nuances, for instance,
if you have 1 field that repeats and you want a specific instance of the
secondary field, but that's usually addressed easily.

CustomerID ProductPurchased
1 Toshiba-TabletPC
1 Imate Jasjar
1 Nikon D20
2 Gateway-Tablet PC
2 Toshiba-Tablet PC

Ok, if you do a select distinct, you'll only get one instance of a product
with customerid = 1 and one with 2. However the one you get may not be the
one you want. So you may need to add another column and use Max, Min or some
other function to get what you want.

HTH,

Bill
 
G

Guest

Thanks for reply, cor and bill

I am trying to get the data from database and then load them into this
datasethelper
class to implement the Select Distinct, what I am asking do I need to
specify the columns like in the example, or copy the data and structure from
the datatable who contains records from database. I am more a C# programmer
than a VB.Net programmer, but in this task I need to used VB.Net.

Code: 
Imports System.Data

Public Class DataSetHelper
Public ds As DataSet
Public Sub New(ByRef DataSet As DataSet)
ds = DataSet
End Sub
Public Sub New()
ds = Nothing
End Sub

Private Function ColumnEqual(ByVal A As Object, ByVal B As Object) As
Boolean

' Compares two values to see if they are equal. Also compares
DBNULL.Value.
' Note: If your DataTable contains object fields, then you must
extend this
' function to handle them in a meaningful way if you intend to group
on them.

If A Is DBNull.Value AndAlso B Is DBNull.Value Then ' both are
DBNull.Value
Return True
End If
If A Is DBNull.Value OrElse B Is DBNull.Value Then ' only one is
DBNull.Value
Return False
End If
Return (A.Equals(B)) ' value type standard comparison
End Function

Public Function SelectDistinct(ByVal TableName As String, ByVal
SourceTable As DataTable, ByVal FieldName As String) As DataTable
Dim dt As DataTable = New DataTable(TableName)
dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)

Dim LastValue As Object = Nothing
For Each dr As DataRow In SourceTable.Select("", FieldName)
If LastValue Is Nothing OrElse Not (ColumnEqual(LastValue,
dr(FieldName))) Then
LastValue = dr(FieldName)
dt.Rows.Add(New Object() {LastValue})
End If
Next dr
If Not ds Is Nothing Then
ds.Tables.Add(dt)
End If
Return dt
End Function

End Class

Inside another Class Library

Public Function GetAllType2Content(ByVal topic As String) As DataTable
Try
Me.sqlText = "Select * From Type2Content Where ParentId In
(Select TopicId From Topics Where Topic = @topic)"
Me.sqlAdapter = New SqlDataAdapter(Me.sqlText, Me.sqlConn)
Me.sqlAdapter.SelectCommand.Parameters.Add("@topic",
SqlDbType.VarChar, 100, "Topic").Value = topic
Dim dsObj As New DataSet
Me.sqlAdapter.Fill(dsObj, "Type2Content")
Me.ds = New DataSetHelper(dsObj)
ds.SelectDistinct("DistinctContents",
dsObj.Tables("Type2Content"), "Heading1")
Me.dtLocal = dsObj.Tables("Type2Content")
Catch ex As Exception
Me.LogError("Unable to get all Type2Content records for
specified parent topic. " & ex.ToString())
Me.dtLocal = Nothing
End Try
Return Me.dtLocal
End Function

Thanks for having patience with me. I am trying to find a shorter version of
using a lot SQL Select Statement w/Distinct keyword and fill data into an a
string array, and use this as filter to get the distinct values for next
columns and so on and so forth.


den2005
 
G

Guest

There is a downloadable .Net Framework Ver2.0, if I installed that would I be
able to used these classes. If not, are there any approach possible in Ver
1.1?

Thanks.

dennis
 
C

Cor Ligthert [MVP]

den,

What is the difference between C# and VBNet beside the style of writting.

Public DataTable Distinct(DataTable dt, string DistinctColumn)
{
DataTable dtclone = dt.Clone();
DataView dv = new DataView(dt);
dv.Sort = DistinctColumn;
string SelOld;
for (int i = 0; dv.Count < 0;i++)
{
if (SelOld <> dv([DistinctColumn].ToString() )
{
DataRow drn = dtclone.NewRow();
for (int y = 0; ItemArray.Length < 0;y++)
{
drn[y] = dv[y];
}
SelOld = dv[DistinctColumn].ToString();
dtclone.Rows.Add(drn);
}
}
return dtclone;
}

Changed in this message so watch typos or whatever.

Cor


den 2005 said:
Thanks for reply, cor and bill

I am trying to get the data from database and then load them into this
datasethelper
class to implement the Select Distinct, what I am asking do I need to
specify the columns like in the example, or copy the data and structure
from
the datatable who contains records from database. I am more a C#
programmer
than a VB.Net programmer, but in this task I need to used VB.Net.

Code: 
Imports System.Data

Public Class DataSetHelper
Public ds As DataSet
Public Sub New(ByRef DataSet As DataSet)
ds = DataSet
End Sub
Public Sub New()
ds = Nothing
End Sub

Private Function ColumnEqual(ByVal A As Object, ByVal B As Object) As
Boolean

' Compares two values to see if they are equal. Also compares
DBNULL.Value.
' Note: If your DataTable contains object fields, then you must
extend this
' function to handle them in a meaningful way if you intend to
group
on them.

If A Is DBNull.Value AndAlso B Is DBNull.Value Then ' both are
DBNull.Value
Return True
End If
If A Is DBNull.Value OrElse B Is DBNull.Value Then ' only one is
DBNull.Value
Return False
End If
Return (A.Equals(B)) ' value type standard comparison
End Function

Public Function SelectDistinct(ByVal TableName As String, ByVal
SourceTable As DataTable, ByVal FieldName As String) As DataTable
Dim dt As DataTable = New DataTable(TableName)
dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)

Dim LastValue As Object = Nothing
For Each dr As DataRow In SourceTable.Select("", FieldName)
If LastValue Is Nothing OrElse Not (ColumnEqual(LastValue,
dr(FieldName))) Then
LastValue = dr(FieldName)
dt.Rows.Add(New Object() {LastValue})
End If
Next dr
If Not ds Is Nothing Then
ds.Tables.Add(dt)
End If
Return dt
End Function

End Class

Inside another Class Library

Public Function GetAllType2Content(ByVal topic As String) As DataTable
Try
Me.sqlText = "Select * From Type2Content Where ParentId In
(Select TopicId From Topics Where Topic = @topic)"
Me.sqlAdapter = New SqlDataAdapter(Me.sqlText, Me.sqlConn)
Me.sqlAdapter.SelectCommand.Parameters.Add("@topic",
SqlDbType.VarChar, 100, "Topic").Value = topic
Dim dsObj As New DataSet
Me.sqlAdapter.Fill(dsObj, "Type2Content")
Me.ds = New DataSetHelper(dsObj)
ds.SelectDistinct("DistinctContents",
dsObj.Tables("Type2Content"), "Heading1")
Me.dtLocal = dsObj.Tables("Type2Content")
Catch ex As Exception
Me.LogError("Unable to get all Type2Content records for
specified parent topic. " & ex.ToString())
Me.dtLocal = Nothing
End Try
Return Me.dtLocal
End Function

Thanks for having patience with me. I am trying to find a shorter version
of
using a lot SQL Select Statement w/Distinct keyword and fill data into an
a
string array, and use this as filter to get the distinct values for next
columns and so on and so forth.


den2005
--
MCP Year 2005, Philippines


W.G. Ryan - MVP said:
Den - I apologize but I don't follow you on this. Can you explain it a
little more. Thx
Click to expand...
Click to expand...
 
G

Guest

Thanks cor for reply.

This method - Public DataTable Distinct(DataTable dt, string DistinctColumn)
I would put it inside the DataSetHelper class. Right?
About the using .Net Framework 2.0, Are you saying that I won't be able to
used this SelectDistinct? Are there any way around this? The bottomline I
like to sort the data without resorting writing more than 20 lines of ocde.
Thanks again.

den2005
 
C

Cor Ligthert [MVP]

Den,

Yes this procedure is a one line method as I showed in this messagethread
already in 2.0

Cor
 
G

Guest

Cor, there is no other way to do this in .Net framework Ver 1.1. I think I'll
do the long way, using a lots os Select Distinct sql queries and loop around
results to get next distinct columsn and so on and so forth and siplay them
at run time.
Thanks for replying.

Dennis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How sort DataView so blanks are at bottom, not top? 1
Comparison of columns using Conditional Formatting 3
Word to Excel: Export comments with text and line number 0
Problems with reading data from excel sheet: Some values are null 1
Query to display all related data from 4-level tree 4
Access Access IRR Function on a Form 0
MS Access Sub form display with all entries 1
When parsing CSV using OleDbConnection, trailing spaces are trimmed 3

Top