Get the previous record of a date

Asked

Viewed 1,227 times

3

I need to make a query, where I need to get the penultimate record, IE, the previous record and the date also.

SELECT DISTINCT
prt_partner.razao_social,
prt_partner.end_cidade,
prt_partner.end_estado,
prt_partner_status.nome,
 (SELECT MAX(_data_registro) FROM prt_partner_historic_status
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

(SELECT MAX(_data_registro) FROM prt_partner_historic_status 
    WHERE _data_registro NOT IN (SELECT MAX(_data_registro) FROM prt_partner_historic_status 
    WHERE prt_partner_historic_status.id_status = prt_partner.id_status)) AS data_anterior


from prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)


WHERE prt_partner._ativo = 1;

I’ve been trying to select the way above, only the "previous data_me" returns wrong

inserir a descrição da imagem aqui

EDITING THE POST

    SELECT DISTINCT
    prt_partner.id_partner as id_parceiro,
    prt_partner.razao_social as nome_empresa,
    prt_partner.end_cidade as cidade,
    prt_partner.end_estado as estado,
    prt_partner_status.nome as status_atual,
    prt_partner_historic_status.dias_entre_status as dias_entre_status,

(SELECT MAX(_data_registro) FROM prt_partner_historic_status
          WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

(SELECT _data_registro FROM prt_partner_historic_status 
          WHERE prt_partner_historic_status.id_status = prt_partner.id_status ORDER BY id_historic_status DESC LIMIT 1 OFFSET 1) AS data_anterior

FROM prt_partner

JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)

JOIN prt_partner_historic_status ON (prt_partner.id_partner = prt_partner_historic_status.id_partner)

WHERE prt_partner._ativo = 1 ORDER BY id_parceiro;

select the top me the following return inserir a descrição da imagem aqui

mysql sql

1 answer

1

Solution for SQL Server

I used the WITH to create a support table with a new column generated by ROW_NUMBER.
In the survey, I searched the record immediately prior to the maximum existing records in the auxiliary table.

See if the following code meets you:

WITH TBL_DATAANTERIOR (_data_registro, numeracao)
AS
(
    SELECT _data_registro, ROW_NUMBER() OVER (ORDER BY _data_registro) numeracao FROM prt_partner_historic_status 
    WHERE _data_registro NOT IN 
            (SELECT MAX(_data_registro) FROM prt_partner_historic_status 
             WHERE prt_partner_historic_status.id_status = prt_partner.id_status)
)
SELECT DISTINCT
prt_partner.razao_social,
prt_partner.end_cidade,
prt_partner.end_estado,
prt_partner_status.nome,
 (SELECT MAX(_data_registro) FROM prt_partner_historic_status
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

(SELECT _data_registro FROM TBL_DATAANTERIOR
 WHERE numeracao = (select MAX(numeracao)-1 from TBL_DATAANTERIOR) AS data_anterior

from prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)
WHERE prt_partner._ativo = 1;

Note: I set up the query directly here, without validating on SQLServer.

Solution for Mysql

Based on the update of the question

SELECT DISTINCT
    prt_partner.id_partner as id_parceiro,
    prt_partner.razao_social as nome_empresa,
    prt_partner.end_cidade as cidade,
    prt_partner.end_estado as estado,
    prt_partner_status.nome as status_atual,
    prt_partner_historic_status.dias_entre_status as dias_entre_status,

    (SELECT MAX(_data_registro) FROM prt_partner_historic_status
     WHERE prt_partner_historic_status.id_status = prt_partner.id_status) AS ultima_data,

    (SELECT _data_registro FROM
     (SELECT _data_registro, id_historic_status FROM prt_partner_historic_status 
      WHERE prt_partner_historic_status.id_status = prt_partner.id_status
      ORDER BY id_historic_status DESC LIMIT 2) TEMP
     ORDER BY id_historic_status ASC LIMIT 1) AS data_anterior

FROM prt_partner
JOIN prt_partner_status ON (prt_partner.id_status = prt_partner_status.id_status)
JOIN prt_partner_historic_status ON (prt_partner.id_partner = prt_partner_historic_status.id_partner)
WHERE prt_partner._ativo = 1 ORDER BY id_parceiro;

  • This with uses at the very beginning?? here accused as error

  • 1

    You are using which SGDB?

  • 1

    I am using mysql

  • This information was missing in your question because WITH is not supported in mysql. I will include a solution for mysql in the answer as soon as possible.

  • Thank you very much

  • 1

    @gabrielfalieri thinking of a palliative solution for MySQL, I think in place of WITH, you could create a view, and in place of row_number, use the @rank:=@rank+1.

  • 1

    Is that this select will become a view, the view can call another view?

  • 1

    Absolutely. At the end of the process, you will have two views.

  • I have no idea how to do this... with select simple, instead of view would not work?

  • As you will create a view from this select, then you cannot have two selects in the same batch. What you would do is separate for a view the first part (id_status, data_record and line numbering) and from the main query, search only the record MAX - 1 of this new view.

  • I edited my sql now, it looks like it’s

  • 1

    With LIMIT and OFFSET solved?

  • Not resolved yet

  • 1

    Use a variable in OFFSET to return max()-1. I can’t test now, but look at this example SELECT max(compoid)-1, t.* FROM Tabela t ORDER BY compoid DESC LIMIT 1;

  • 1

    Even though the id is not sequential? Type, for example, in select not a proper order?

  • 1

    It is that the idea of max()-1 does not refer to the id of the table, but rather, the line in question. Creating a new column with the line number, we could take the second-to-last line with the offset. You can try with id_status < that max(id_status) too.

  • 1

    Hello @gabrielfalieri, consider accepting my answer if it has been useful to you. If you think she’s incomplete or doesn’t respond to you, make the appropriate comments so I can improve her.

Show 12 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.