description: first n is include in sql statement but if I want selected first n records for each group which is in group by clause I need usig row_number() over (partition by )
but I don't if firebird support row_number(other database support this)

CREATE TABLE employees (
employee_id integer PRIMARY KEY,
employee_name text
)

CREATE TABLE departments (
department_id integer PRIMARY KEY,
department_name text
)

CREATE TABLE emp_dep (
ID_employee integer,
ID_department integer,
date_of_insert date default NOW(),
mytime time default NOW(),
PRIMARY KEY(ID_employee, ID_department)
)

select emp_dep.date_of_insert, emp_dep.mytime, t3.department_name , t2.employee_name from employees t2,departments t3,emp_dep
where emp_dep.ID_employee=t2.employee_id and emp_dep.ID_department=t3.department_id
group by emp_dep.date_of_insert,t3.department_name , t2.employee_name, emp_dep.mytime
order by emp_dep.date_of_insert desc, emp_dep.mytime desc
limit by group(t3.department_name,3)

for first 3

or
select first 3 by group(t3.department_name) emp_dep.date_of_insert, emp_dep.mytime, t3.department_name , t2.employee_name from employees t2,departments t3,emp_dep
where emp_dep.ID_employee=t2.employee_id and emp_dep.ID_department=t3.department_id
group by emp_dep.date_of_insert,t3.department_name , t2.employee_name, emp_dep.mytime
order by emp_dep.date_of_insert desc, emp_dep.mytime desc

=>

first n is include in sql statement but if I want selected first n records for each group which is in group by clause I need usig row_number() over (partition by )
but I don't if firebird support row_number(other database support this)

CREATE TABLE employees (
employee_id integer PRIMARY KEY,
employee_name text
)

CREATE TABLE departments (
department_id integer PRIMARY KEY,
department_name text
)

CREATE TABLE emp_dep (
ID_employee integer,
ID_department integer,
date_of_insert date default NOW(),
mytime time default NOW(),
PRIMARY KEY(ID_employee, ID_department)
)

select emp_dep.date_of_insert, emp_dep.mytime, t3.department_name , t2.employee_name from employees t2,departments t3,emp_dep
where emp_dep.ID_employee=t2.employee_id and emp_dep.ID_department=t3.department_id
group by emp_dep.date_of_insert,t3.department_name , t2.employee_name, emp_dep.mytime
order by emp_dep.date_of_insert desc, emp_dep.mytime desc
limit by group(t3.department_name,3)

for first 3

or
select first 3 by group(t3.department_name) emp_dep.date_of_insert, emp_dep.mytime, t3.department_name , t2.employee_name from employees t2,departments t3,emp_dep
where emp_dep.ID_employee=t2.employee_id and emp_dep.ID_department=t3.department_id
group by emp_dep.date_of_insert,t3.department_name , t2.employee_name, emp_dep.mytime
order by emp_dep.date_of_insert desc, emp_dep.mytime desc

with one or more parameters for limit or first.

Link: This issue is replaced by CORE1688 [ CORE1688 ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

status: Resolved [ 5 ] => Closed [ 6 ]