Show last record entered, if it appears repeated

Question

Show last record entered, if it appears repeated

Navigation

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

1

Today I bring a question about How to make a query?

It turns out that I have a table called x, which has 3 or more records (ID, MAC, DATE_CREATION).

Now I want to make a SELECT which selects a range of dates eg

SELECT
 ID, MAC, FECHA_CREACION
FROM x
WHERE
  FECHA_CREACION BETWEEN 'ayer (00-00-00)' and 'hoy (00-00-00)'

Result:

<table border='0'>
<tr>
  <td>1</td>
  <td>bcc810139fc0</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>2</td>
  <td>ac202e43ef20</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>3</td>
  <td>ac202e444e20</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>4</td>
  <td>bcc810139fc0</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>5</td>
  <td>ac202e43ef20</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>6</td>
  <td>ac202e444e20</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>7</td>
  <td>bcc810139fc0</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>8</td>
  <td>bcc810139fc0</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>9</td>
  <td>ac202e43ef20</td>
  <td>2018-10-24 08:35:13</td>
<tr>
<tr>
  <td>10</td>
  <td>bcc810139fc0</td>
  <td>2018-10-25 08:35:13</td>
<tr>
<tr>
  <td>11</td>
  <td>ac202e444e20</td>
  <td>2018-10-25 08:35:13</td>
<tr>
</table>

And everything works very well, but now imagine that the range of dates I have several MACs equal (if possible, for the idea I develop), then there goes my question How do I make a query which if it finds several MAC same in that range of dates, show the last one entered (DATE_CREATION)?

Thank you very much.

mysql sql php

asked by JuanD 25.10.2018 в 18:12

source

2 answers

how do I start dual ubuntu if my computer starts with windows 10? [closed] Show message if php records do not appear

score 1 · Answer 1

Good morning mate,

I have done a little exercise and it has worked for me, I hope it works for you:

SELECT ID, MAC, FECHA_CREACION
  FROM x A
 WHERE FECHA_CREACION BETWEEN 'ayer (00-00-00)' AND 'hoy (00-00-00)'
   AND FECHA_CREACION = (SELECT MAX(FECHA_CREACION)
                           FROM x B
                          WHERE A.FECHA_CREACION = B.FECHA_CREACION);

Here what I did was create a subquery of the same table to compare the value of DATE_CREATION and get the maximum or last.

Best regards,

score 1 · Answer 2

You could use Rownumber to count the repeated records and only show the first ones that you return:

WITH TableWRowNumber
AS
(
    SELECT A1.ID,A1.MAC,A1.FECHA_CREACION,
    ROW_NUMBER() OVER (PARTITION BY A1.MAC ORDER BY A1.MAC DESC) AS RowNumber FROM Macs A1
    WHERE FECHA_CREACION BETWEEN dateadd(d, (datediff(d, 0, GetDate() - 1)) , 0) and CONVERT(DateTime, GETDATE(),101)
       and FECHA_CREACION = ( 
    select MAX(A2.FECHA_CREACION) from MACs A2  
    WHERE A1.MAC = A2.MAC
    ) 
)

SELECT ID,MAC,FECHA_CREACION FROM TableWRowNumber WHERE ROWNUMBER = 1 ORDER BY 
   CONVERT(DateTime, FECHA_CREACION,101)  DESC

And in the end you only select those with RowNumber = 1 and order them from largest to smallest.

MySQL introdujo la ROW_NUMBER() función desde la versión 8.0. La ROW_NUMBER()es una función de ventana o función analítica que asigna un número secuencial a cada fila a la que se aplicó comenzando con uno.

Keep in mind that if you use MySQL with a version lower than 8.0, can emulate some functionalities of the ROW_NUMBER () function using other techniques.

I hope and serve you. Greetings.