Home / Questions / 483174
Accepted
Rodrigo Cortés
Asked: 2022-09-09 22:29:16 +0800 CST 2022-09-09 22:29:16 +0800 CST

How to get the first and last value of a range/group in MySQL?

  • 772

Good morning, I'm stuck on a query that I can't solve, I'll explain.

I have a database with the following data:

And I need to perform a query where the results have the following columns (data already grouped):

THIS RESULT IS WRONG. This is what I want to get to:

My problem is the column that says 'START' and 'END' referring to a grouping by CENTER and UR that I did, what is the problem itself? I obtain the first and last value of that group with the MIN and MAX functions, the problem with these functions is that they search with respect to the number and I am interested in not changing the order in which the receipts come, (Regardless of whether they are larger What others)

For example, in the UR FOR I would like to know how I could get the first value which is: 110089668 and the last which is 100788846.

Ordering by rfc or grouping I don't get this result, any ideas? Thanks.

My table consists of the following:

   CREATE TABLE `for1421` (
  `RFC` varchar(13) DEFAULT NULL,
  `NOMB` varchar(55) DEFAULT NULL,
  `EDDO` varchar(2) DEFAULT NULL,
  `MUNI` varchar(3) DEFAULT NULL,
  `CR` varchar(10) DEFAULT NULL,
  `TAB` varchar(3) DEFAULT NULL,
  `AA` varchar(4) DEFAULT NULL,
  `PP` varchar(4) DEFAULT NULL,
  `UR` varchar(3) DEFAULT NULL,
  `PTDA` varchar(5) DEFAULT NULL,
  `COD` varchar(7) DEFAULT NULL,
  `PGAI` varchar(5) DEFAULT NULL,
  `NUM` varchar(4) DEFAULT NULL,
  `INMDO` varchar(2) DEFAULT NULL,
  `TITRA` varchar(2) DEFAULT NULL,
  `HORASI` varchar(1) DEFAULT NULL,
  `NIVPTO` varchar(2) DEFAULT NULL,
  `RGO` varchar(1) DEFAULT NULL,
  `PORCPTO` varchar(8) DEFAULT NULL,
  `PERPAGI` varchar(8) DEFAULT NULL,
  `PERPAGF` varchar(8) DEFAULT NULL,
  `TIPAG` varchar(1) DEFAULT NULL,
  `NUMCHE` text,
  `DIGV` varchar(1) DEFAULT NULL,
  `CTA` varchar(12) DEFAULT NULL,
  `TIBA` varchar(2) DEFAULT NULL,
  `INSTRU` varchar(2) DEFAULT NULL,
  `I1` double DEFAULT NULL,
  `I2` double DEFAULT NULL,
  `I3` double DEFAULT NULL,
  `TTR` varchar(2) DEFAULT NULL,
  `NUMREG` varchar(10) DEFAULT NULL,
  `PRDNAME` varchar(11) DEFAULT NULL,
  `CURP` varchar(18) DEFAULT NULL,
  `EDO` varchar(2) DEFAULT NULL,
  `TIPO` varchar(2) DEFAULT NULL,
  `QNA` varchar(2) DEFAULT NULL,
  `ANIO` varchar(4) DEFAULT NULL,
  `QNAC` varchar(2) DEFAULT NULL,
  `ANIOC` varchar(4) DEFAULT NULL,
  `comisionado` varchar(15) DEFAULT 'NO',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5107 DEFAULT CHARSET=latin1

My question is the following:

SELECT emp.CR,cr.DESCRIPCION,cr.JURISDICCION,MIN(numche),MAX(numche),MIN(id),MAX(id)
FROM FOR1421 emp 
INNER JOIN centros AS cr ON emp.CR = cr.CR  
WHERE EMP.INSTRU != '00' AND emp.PRDNAME = 'PRDO140' AND emp.comisionado = 'NO' AND cr.JURISDICCION = 'J-2'
GROUP BY emp.cr,cr.JURISDICCION,emp.UR
ORDER BY cr.JURISDICCION,CR.CR,emp.ur,emp.RFC,emp.INSTRU,emp.id;

My center table with which I do the inner join

CREATE TABLE `centros` (
  `CR` varchar(255) NOT NULL,
  `DESCRIPCION` varchar(255) DEFAULT NULL,
  `JURISDICCION` varchar(255) DEFAULT NULL,
  `PAGADOR` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The problem itself lies in obtaining the receipt (NUMCHE).

Attached some data:

rfc cr ur number instructor
CUMA860108 0030 FO2 100788834 10
GAOM830516 0030 FO2 100788835 10
NUPP720529 0030 FO2 100788836 10
PEPN741214 0030 FO2 100788837 10
AARV890203 0030 FO3 100788838 10
COSA851223 0030 FO3 110089667 eleven
FOIV761231 0030 FOR 110089668 eleven
GAMM740928 0030 FOR 100788839 10
HEOF890511 0030 FOR 100788840 10
LOJI840229 0030 FOR 100788841 10
MACG750830 0030 FOR 100788842 10
MIUI871007 0030 FOR 110089669 eleven
PAEL830703 0030 FOR 100788843 10
RAMR880809 0030 FOR 100788844 10
SACA600801 0030 FOR 100788845 10
SAMR770216 0030 FOR 100788846 10
mysql

2 Answers

  1. Best Answer

    After a few tries, I managed to solve my problem.

    A little archaic perhaps, but functional, if anyone has any other suggestions, welcome.

    What I did was the following query:

    SUBSTRING((MIN(CONCAT(rfc,"-",numche))),15,10) AS 'INICIO',SUBSTRING((MAX(CONCAT(rfc,"-",numche))),15,10) AS 'FINAL'

    "Nest" it in some way with the rfc since they all followed this order and thus the data would not be ordered with respect to min and max.

    • 2

  2. As of MySql 8.0 you can use Window functionsto get the first and last numcheof each urby sorting on the column rfc. I don't know what version of MySQL you are using.

    The query is created from the structure of the test data you shared (not the original tables, since I did not have test data for the original tables):

    SELECT DISTINCT cr, 
                FIRST_VALUE(numche) OVER (PARTITION BY ur ORDER BY rfc ASC) AS inicio,
                FIRST_VALUE(numche) OVER (PARTITION BY ur ORDER BY rfc DESC) AS final,
                COUNT(numche) OVER (PARTITION BY ur) AS registros,
                ur
FROM tabla;

    Output (with the sample data from the question):

    cr beginning final records ur
    0030 100788834 100788837 4 FO2
    0030 100788838 110089667 two FO3
    0030 110089668 100788846 10 FOR
    • 1