Jump to content
  • 0
bbeshlian

Select Distinct Records

Question

Hello,

I have created a view which is returning duplicates of each record. Is there a way to prevent this from happening like the SELECT DISTINCT does in SQL in either the view or when I use this view as a source for a datapage?

Thanks.

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

What kind of page are you using? I know there is a "Distinct Options" checkbox in the Configure Search Fields Wizard.

*Nevermind. I believe that checkbox is to limit duplicates in a dropdown used in search. I'm not sure if it affects results.

Share this post


Link to post
Share on other sites
  • 0

Maybe I did not make myself clear enough. The view that I built and want to use as the datasource for my datapage has duplicate records. I do not wish to have these duplicate records either when I run the view or displayed in the datapage. Is there a function similar to the SELECT DISTINCT in SQL which will either stop the duplicate records in the view or when I use the view as the datasource for my datapage.

Thanks.

Share this post


Link to post
Share on other sites
  • 0

The short answer is no, SELECT DISTINCT is not available for a view (or datapage), or GROUP BY aggregate functions.  If you search deeper here you'll find some creative ways to get the job done.  Somewhat clunky but I have used grouping on the datapage along with some css hacking to hide the child rows.  Also clunky but effective could be adding a "placeholder" table (or view) that is distinct to base your report datapage on and then use a keyID field and calculated fields for the report row fields where you can define the SQL to create a distinct result.  Feel free to add some votes to the ideabox submission for this.

Can you provide an example of the data structure?

Share this post


Link to post
Share on other sites
  • 0

Hi @bbeshlian,

The solution for this issue is to use a table which has ID field marked as unique field in the table. Use this field as the data source of your report DataPage.  To show the rest of the fields in the report, use calculated fields with SQL queries to query the view based on the same ID value. Be sure to include a Where condition which will help only pull a certain record for the respective ID value.

When using a view in the calculated field, add '_v_' to the name of the table like shown below:

select field_name1 from _v_viewname where field_name2='..........'

I hope that helps.

Regards,

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...