| group every 5 records [message #686863] |
Fri, 27 January 2023 10:15  |
 |
deahayes
Messages: 6
Registered: January 2023
|
Junior Member |
|
|
hello all,
Im looking to create a sql query where I can group every 5 rows. I plan to use this on a BI publisher template where it should only show 5 records per template.
I am thinking query should look like this: any suggestions on how to create it.
grpid project id
1 A 111
1 B 123
1 D 223
1 C 212
1 E 345
2 F 667
2 A 55
2 B 44
2 D 33
2 C 3322222
3 E 33
3 F 988
|
|
|
|
| Re: group every 5 records [message #686867 is a reply to message #686863] |
Fri, 27 January 2023 11:12  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am not familiar with BI, so I don't know if there is some built-in method or not, so this is just responding to the question about the SQL query to provide a group id. It does not look like your data is in any particular order and you did not provide create and insert statements for sample data, so the following is just a brief simulation of one method, using Oracle demo data. There may also be newer better methods.
SCOTT@orcl_12.1.0.2.0> column project format a7
SCOTT@orcl_12.1.0.2.0> select ceil(rownum/5) grpid, substr(job,1,1) project, empno id
2 from emp
3 /
GRPID PROJECT ID
---------- ------- ----------
1 C 7369
1 S 7499
1 S 7521
1 M 7566
1 S 7654
2 M 7698
2 M 7782
2 A 7788
2 P 7839
2 S 7844
3 C 7876
3 C 7900
3 A 7902
3 C 7934
14 rows selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
