How to select only the first records of a ranking when there are several ranking groups from a procedure stored in MySQL?

Question

How to select only the first records of a ranking when there are several ranking groups from a procedure stored in MySQL?

Navigation

#1 by (1 votes)

0

I have the following procedure stored in MySQL, it works for me but nevertheless it brings me more records, I mean there are several records that coincide in almost all the fields except in the series and in the date, so I decided to order them as in a ranking.

CREATE DEFINER='usuarioroot'@'%' PROCEDURE 'spRank'()
BEGIN

set @report_rank := 0;
set @current  := 0; 

select a.*, 
       @report_rank := IF(@current  = clave , @report_rank + 1, 1) AS report_rank,
                                            @current  := clave              
  from (
    SELECT tabla1.serie,Count(*) as cantSerie,fecha,  
        CASE WHEN DateDiff(fecha,Now())<0 Then 0 Else  
        CASE WHEN DateDiff(fecha,Now())>0 AND DateDiff(fecha,Now())<20 Then 1 
                        Else 2 End End As Codfecha, 
                        DateDiff(Max(fecha),Now()) as DiasExp, 
                        CASE WHEN IsNull(seriePdf) Then 'No' Else 'Si' end as EnPdf,  
                        tabla2.clave,campo4 as antiguo 
                        From bd1.tabla1, bd1.tabla2, bd2.tabla3  ,
                            (SELECT tabla2.clave 
                                From bd1.tabla2, bd2.tabla3 
                                Where tabla2.clave = bd2.tabla3.clave 
                                Group By tabla2.clave) 
                                Subclave
                                Where tabla2.clave = bd2.tabla3.clave  
                                AND tabla1.serie=tabla2.serie  
                                and subclave.clave = tabla2.clave
                                GROUP BY tabla2.clave,tabla1.serie 
                                ) a
            ORDER BY antiguo,clave,fecha DESC,cantSerie,serie;

END

I can get the following result:

  serie    cantSerie         fecha       Codfecha   DiasExp  EnPdf  clave  antiguo report_rank @current :=clave

123AB0123      5     2016-04-26 00:00:00     0       -114      Si   01A1     NO        1                01A1
123AC0123      4     2014-03-26 00:00:00     0       -876      Si   01A1     NO        2                01A1
123BC0123      1     2013-03-30 00:00:00     0       -1237     Si   01A1     NO        3                01A1
123BD0123      5     2013-03-30 00:00:00     0       -1237     Si   01A1     NO        4                01A1
124BF0123      1     2016-06-10 00:00:00     0       -69       Si   01A2     NO        1                01A2
123BD0023      1     2013-03-30 00:00:00     0       -1237     Si   01A2     NO        2                01A2

But I just want the records they have in the report_rank = 1, try using the clause where: where @report_rank = 1 but it does not bring me any record and it does not mark me an error either, also I would like it in the final result not you see the last 2 fields: report_rank @current :=clave

In a few words I want to get the following result:

  serie    cantSerie         fecha       Codfecha   DiasExp  EnPdf  clave  antiguo 

123AB0123      5     2016-04-26 00:00:00     0       -114      Si   01A1     NO        
124BF0123      1     2016-06-10 00:00:00     0       -69       Si   01A2     NO

mysql stored-procedure

asked by El Cóndor 18.08.2016 в 14:37

source

1 answer

Load data by ajax with Jquery and place parameters in url Go from Linq to Sql

score 1 · Accepted Answer

Since you are trying to filter based on a deducted column, it is not possible to filter using WHERE which filters the results a priori based on the rows. This is why the keyword HAVING exists which filters afterwards, that is, filters based on the results already obtained.

Your query would look like this:

select a.*, 
       @report_rank := IF(@current  = clave , @report_rank + 1, 1) AS report_rank,
                                            @current  := clave              
  from (
    SELECT tabla1.serie,Count(*) as cantSerie,fecha,  
        CASE WHEN DateDiff(fecha,Now())<0 Then 0 Else  
        CASE WHEN DateDiff(fecha,Now())>0 AND DateDiff(fecha,Now())<20 Then 1 
                        Else 2 End End As Codfecha, 
                        DateDiff(Max(fecha),Now()) as DiasExp, 
                        CASE WHEN IsNull(seriePdf) Then 'No' Else 'Si' end as EnPdf,  
                        tabla2.clave,campo4 as antiguo 
                        From bd1.tabla1, bd1.tabla2, bd2.tabla3  ,
                            (SELECT tabla2.clave 
                                From bd1.tabla2, bd2.tabla3 
                                Where tabla2.clave = bd2.tabla3.clave 
                                Group By tabla2.clave) 
                                Subclave
                                Where tabla2.clave = bd2.tabla3.clave  
                                AND tabla1.serie=tabla2.serie  
                                and subclave.clave = tabla2.clave
                                GROUP BY tabla2.clave,tabla1.serie 
                                ) a
       HAVING report_rank = 1 -- <== filtra a posteriori
            ORDER BY antiguo,clave,fecha DESC,cantSerie,serie;

Another possible option would have been to calculate this value within WHERE , but this makes it less than optimal, since it would be calculating this for each row, possibly doubling the response time.