ccilliers
Posts: 2
|
| Posted: 06/22/2010, 7:19 AM |
|
I have a report in a Codecharge Page that uses a query to generate a set of results grouped using the SQL Group By function. However some of the data is duplicated, except for 1 column, for instance a table with the following data:
Name......SurName.......City..............Phone
John........Macintosh......Boston..........5550213
John........Macintosh......Boston..........5550231
Peter.......O'Reilly.........Washington...1234567
Peter.......O'Reilly.........Washington...1234565
I would like to select The first entry of each of these duplicates. In this case I want
John........Macintosh......Boston..........5550213
Peter.......O'Reilly.........Washington...1234567
I have done this in Access using the First() function, but CodeCharge does not support doing this in the SQL Query. Is there any other way to do it? I am using PHP and connecting to a MS SQL 2005 server.
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 06/22/2010, 6:54 PM |
|
ccilliers,
Try "Select distinct"
for example: Select distinct name, surname, city, phone from name table
Here is a link with more information: http://www.w3schools.com/sql/sql_distinct.asp
Try it on the database first to see what how the results come out.
|
|
|
|
mrachow
Posts: 509
|
| Posted: 06/27/2010, 4:38 AM |
|
I think Quote :distinct wan't help in this case.
A more pretty looking solution than the original could be
Name......SurName.......City..............Phone
John........Macintosh......Boston..........5550213
......................................................5550231
Peter.......O'Reilly.........Washington...1234567
......................................................1234565
The report supports blanking out next line same content easily.
_________________
Best regards,
Michael |
|
|
|
Vasiliy
Posts: 378
|
| Posted: 06/28/2010, 2:36 PM |
|
You can do this w/o SQL.
Declare page vars: LastName, LastSurName, LastCity
On Event "After Each Row"
________________
MyGrid.Name.Visible = True
MyGrid.SurName.Visible = True
MyGrid.City.Visible = True
If MyGrid.Name = LastName AND MyGrid.SurName = LastSurName AND MyGrid.City = LastCity Then
MyGrid.Name.Visible = False
MyGrid.SurName.Visible = False
MyGrid.City.Visible = False
End If
LastName = MyGrid.Name
LastSurName = MyGrid.SurName
LastCity = MyGrid.City
________________
_________________
Vasiliy |
|
|
|
ccilliers
Posts: 2
|
| Posted: 06/29/2010, 1:29 AM |
|
Thank you for the replies. Sadly, my problem is still not solved.
The Distinct function only seems to work when two results are exactly the same, so it doesn't work in my case.
Simply setting each field's "Hide Duplicates" property is not an option, because for the following set of data:
Name......SurName.......City..............Phone
John........Macintosh......Boston..........5550213
John........Macintosh......Boston..........5550231
Peter.......O'Reilly.........Boston..........1234567
Peter.......O'Reilly.........Boston..........1234565
I would get the result:
Name......SurName.......City..............Phone
John........Macintosh......Boston..........5550213
......................................................5550231
Peter.......O'Reilly.............................1234567
......................................................1234565
I would like Boston to be in the row for Peter as well. I would be able to do bypass this by coding my own check to Hide duplicates in the BeforeShowRow or AfterEachRow event, but seeing as I'm working with a report and not a grid, these events do not exist.
I also found out that the First() Function in Access supposedly picks a random value. This is exactly what I want. I do not care which phone number is the correct one, I just want one line.
Please take note that I used arbitrary set of data, the set I am working on has many more columns, and many more records, and for the column with the difference (phone no) it is irrelevant which one of the two results gets picked.
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 08/15/2010, 8:23 AM |
|
ccilliers,
Are you still working on this issue??
|
|
|
|
|
