Group plots showing the value of the first and last plot

Question

I have a table of plots with the following fields (among others): inscricao (Customer Registration), nrparcela (Plot number), dt_vencimento (Due Date) and vlrparcela (Value of the Plot).

If the customer has 10 installments, there will be 10 records in the table with the column nrparcela numbered from 1 to 10, with respective due dates and (varied) values for that customer.

How to group these records by selecting the number of the first installment (usually '1'), the number of the last installment, the value of the first installment and the value of the last installment?

The value of each parcel may vary, there are cases where the value of each parcel is different from the other values of that parcel.

That’s all I got:

SELECT inscricao, MIN(nrparcela) AS primeira_parcela, 
       MAX(nrparcela) AS ultima_parcela
  FROM parcelas 
 GROUP BY inscricao 
 ORDER BY inscricao

Answer 1

Correlated sub-consultations (queries within the select) can be very bad for performance. So it is better to one of the following two solutions.

The first using distinct on

select *
from 
    (
        select distinct on (inscricao)
            inscricao, nrparcela as primeira_parcela, vlr_parcela as vlr_primeira_parcela
        from parcelas
        order by inscricao, primeira_parcela
    ) p
    inner join
    (
        select distinct on (inscricao)
            inscricao, nrparcela as ultima_parcela, vlr_parcela as vlr_ultima_parcela
        from parcelas
        order by inscricao, ultima_parcela desc
    ) u using (inscricao)

The second with two joints

select s.inscricao,
    primeira_parcela, p.vlrparcela as vlr_primeira_parcela,
    ultima_parcela, u.vlrparcela as vlr_ultima_parcela
from
    (
        select inscricao, max(nrparcela) as ultima_parcela, min(nrparcela) as primeira_parcela
        from parcelas
        group by inscricao
    ) s
    inner join
    parcelas p on p.inscricao = s.inscricao and p.nrparcela = s.primeira_parcela
    inner join
    parcelas u on u.inscricao = s.inscricao and u.nrparcela = s.ultima_parcela

Just testing to see which has better performance

Answer 2

Here is my solution:

Structure:

create table PARCELA (
  inscricao int not null,
  nrparcela int not null,
  dt_vencimento datetime,
  vlrparcela decimal(15,2)
);

alter table PARCELA add constraint PARCELA_PK primary key (inscricao, nrparcela);


insert into PARCELA values (1, 1, '2014/01/01', 10.01);
insert into PARCELA values (1, 2, '2014/01/02', 10.02);
insert into PARCELA values (1, 3, '2014/01/03', 10.03);
insert into PARCELA values (1, 4, '2014/01/04', 10.04);

insert into PARCELA values (2, 1, '2014/02/01', 110.01);
insert into PARCELA values (2, 2, '2014/02/02', 110.02);
insert into PARCELA values (2, 3, '2014/02/03', 110.03);
insert into PARCELA values (2, 4, '2014/02/04', 110.04);
insert into PARCELA values (2, 5, '2014/02/05', 110.05);

SQL

select
  a.inscricao, min(a.nrparcela), 
  (select b.vlrparcela from PARCELA b where b.inscricao = a.inscricao and b.nrparcela = min(a.nrparcela)),
  max(a.nrparcela), 
  (select b.vlrparcela from PARCELA b where b.inscricao = a.inscricao and b.nrparcela = max(a.nrparcela))
from 
  PARCELA a  
group by
  a.inscricao

http://sqlfiddle.com/#! 3/23410f

I used SQL Server because I don’t remember the types in postgresql