Custom Query

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Custom Query

Postby dekumar2 » Thu May 11, 2017 7:02 am

How to do grouping in servoy?
dekumar2
 
Posts: 8
Joined: Thu May 11, 2017 6:59 am

Re: Custom Query

Postby ROCLASI » Thu May 11, 2017 7:48 am

Hi,

To be able to give a proper answer can you tell us what are you trying to accomplish exactly?
Do you just want to aggregate something? Or flatten a result set to get the duplicates out? Or something else?
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Custom Query

Postby dekumar2 » Fri May 12, 2017 11:14 am

Actually, We have a table in which there are 109 columns and in which fixed four columns have same values then we want to do grouping on those columns. On the basis of grouping we want to fetch distinct ids.Please help me.
dekumar2
 
Posts: 8
Joined: Thu May 11, 2017 6:59 am

Re: Custom Query

Postby ROCLASI » Fri May 12, 2017 11:32 am

Hi,

You want to return 1 row per distinct group?
Because ID's (PK's) are per definition distinct per row you would never get a distinct ID per group. Unless you say you want to get the first ID of the distinct group.
Is that want you try to do?
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Custom Query

Postby dekumar2 » Fri May 12, 2017 11:40 am

Ok but how to get the first ID of the distinct group?
dekumar2
 
Posts: 8
Joined: Thu May 11, 2017 6:59 am

Re: Custom Query

Postby ROCLASI » Fri May 12, 2017 11:53 am

You can use a Window Function for this:

Code: Select all
SELECT id, row_number() OVER (PARTITION BY col1, col2, col3 col4) AS rownr FROM myTable

This will give each row in the group a row number, so if you group has 3 rows you get number 1, 2, 3. All you have to do is select only rows with row number 1.
However you can't address Window Functions in a WHERE clause so you have to wrap it in another select, making it essentially a view.
Code: Select all
SELECT id
FROM (
    SELECT id, row_number() OVER (PARTITION BY col1, col2, col3 col4) AS rownr FROM myTable
) t
WHERE rownr = 1

Window Functions are supported by pretty much all database vendors.

So you could load this in a foundset like so:
Code: Select all
foundset.loadRecords("SELECT id \
    FROM ( \
        SELECT id, row_number() OVER (PARTITION BY col1, col2, col3 col4) AS rownr FROM myTable \
    ) t \
    WHERE rownr = 1");


Hope this helps.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium


Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot] and 5 guests