Group plots showing the value of the first and last plots

Question

Group plots showing the value of the first and last plots

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have a parcel table with the following fields (among others): inscricao (Customer Registration), nrparcela (Portion Number), dt_vencimento (Expiration Date) and vlrparcela Plot).

If the client has 10 parcels, there will be 10 records in the table with the column nrparcela numbered from 1 to 10, with their due dates and (variant) values for that client.

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

The value of each plot may vary, with cases where the value of each plot is different from the other values of that plot.

I just got this:

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

postgresql

asked by anonymous 07.05.2014 / 16:45

2 answers

1

Correlated subqueries (queries within the select list) can be very bad for performance. So one of the following two solutions is best.

The first one 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 joins

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

Only testing to see which performs best

30.09.2014 / 14:17

Multiple Definitions in the moc.o file of a class Problems with Android Studio [closed]

score 1 · Accepted Answer

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

link

I used SQL Server because I do not remember the types in postgresql