# select distinct value from a table field **URL:** \ **Category:** Classic Servoy\ **Created:** [July 4, 2007, 11:15am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771 "2007-07-04T11:15:39Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![servoy\_help](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@servoy\_help](https://forum.servoy.com/u/servoy_help)\ **Post date:** [July 4, 2007, 11:15am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/1 "2007-07-04T11:15:39Z")
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 ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [July 4, 2007, 11:27am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/2 "2007-07-04T11:27:57Z")
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. ---
**Author:** ![ars](https://avatars.discourse-cdn.com/v4/letter/a/e19adc/32.png) [@ars](https://forum.servoy.com/u/ars)\ **Post date:** [July 4, 2007, 11:29am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/3 "2007-07-04T11:29:46Z")
> 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. ![:)]( "Smile") ---
**Author:** ![servoy\_help](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@servoy\_help](https://forum.servoy.com/u/servoy_help)\ **Post date:** [July 4, 2007, 11:42am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/4 "2007-07-04T11:42:40Z")
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. ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [July 4, 2007, 11:49am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/5 "2007-07-04T11:49:25Z")
> 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. ---
**Author:** ![ars](https://avatars.discourse-cdn.com/v4/letter/a/e19adc/32.png) [@ars](https://forum.servoy.com/u/ars)\ **Post date:** [July 4, 2007, 11:58am UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/6 "2007-07-04T11:58:37Z")
> 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. ---
**Author:** ![servoy\_help](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@servoy\_help](https://forum.servoy.com/u/servoy_help)\ **Post date:** [July 4, 2007, 12:01pm UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/7 "2007-07-04T12:01:18Z")
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 … ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [July 4, 2007, 12:10pm UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/8 "2007-07-04T12:10:34Z")
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. ---
**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\ **Post date:** [July 4, 2007, 12:12pm UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/9 "2007-07-04T12:12:06Z")
Then you could write ```auto '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. ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [July 4, 2007, 12:16pm UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/10 "2007-07-04T12:16:33Z")
Wouldn’t a simply join do the trick ? I believe that is less expensive as well. ---
**Author:** ![servoy\_help](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@servoy\_help](https://forum.servoy.com/u/servoy_help)\ **Post date:** [July 4, 2007, 12:17pm UTC](https://forum.servoy.com/t/select-distinct-value-from-a-table-field/7771/11 "2007-07-04T12:17:49Z")
Ok, I will use sql query . Thanks for code . ## Regards servoy\_help