select distinct value from a table field

Classic Servoy
1

Hi All ,
I need to retrieve distinct value from a table field . eg . user name from user table to create a list of distinct user name .

Thanks in advance

2

Hi servoy_help,

You didn’t tell us how you want to use that ‘list’.
If you setup a valuelist using that column you already have a distinct list with values.
If you just want to use that distinct list in a method you can use SQL and the databaseManager to get these distinct values.

Hope this helps.

3

servoy_help:
Hi All ,
I need to retrieve distinct value from a table field

You can use SQL query for that…(Using DISTINCT clause)
By using databaseManager.getDataSetByQuery(), you can execute your query.

Hope it will help. :)

4

Let me explain the total scenario .
I have to retrieve this distinct user names from table and need to show some statistics based on that value to this page it self .
I have used relation but it is also giving multiple value .
I think , If I use getDataSetByQuery() then the link will be break with the foundset .
also If I use sql query then how I will represent it in a list.

5

servoy_help:
I have to retrieve this distinct user names from table and need to show some statistics based on that value to this page it self .

Can you explain a little more what you mean by this.
How do you get these statistics, do you use a record view or some list view, etc.

Also there are several ways to display your data without touching the foundset.

6

ROCLASI:
Also there are several ways to display your data without touching the foundset.

You can convert a your foundset to dataset.
by using convertToDataSet(foundset),

A dataset is really just a two-dimensional data container, kind of like a snapshot of a foundset.

7

I am using table view to view user names . And statistics includes some related values with this user name from user table and another related table .
I m giveng u the table structures

user table

user_id pk
user_name
…
…

document

doc_code pk
details
…
…
user_id (from user table)
…
…
Now from this table structure I want to get all distinct user name of the users who have posted document .

I think now it will help u to solve my problems
Is it ossible to make relation between two table to get distinct user name

Thanks …

8

You could select the whole set in 1 SQL statement and display the data as InMemDataGrid (bean).
This way you don’t touch your foundset.

Another way is to use SQL again but only query the distinct PK’s and set the foundset. To restore your foundset you simply use controller.loadAllRecords.

9

Then you could write

'SELECT user_name FROM users WHERE EXISTS (SELECT * FROM document WHERE users.user_id = documents.user_id)'

That will give you all user names that have at least one record in documents.

10

Wouldn’t a simply join do the trick ? I believe that is less expensive as well.

11

Ok, I will use sql query .
Thanks for code .

Regards

servoy_help