Custom Query

[dekumar2]

How to do grouping in servoy?

[ROCLASI]

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?

[dekumar2]

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.

[ROCLASI]

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?

[dekumar2]

Ok but how to get the first ID of the distinct group?

[ROCLASI]

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.