Select statement selecting wrong data

[Adamhumbug]

I have quite a long query that is supposed to select the highest id to get the row.

                SELECT MAX(dr.id), device_id, status_id, action_time, d.name as deviceName, d.type_id, d.notes, l.name, l.scanning_for, 				ds.name as deviceStatus
                from deployment_register dr
                inner join location l on dr.location_id = l.id
                inner join device d on dr.device_id = d.id
                inner join device_status ds on dr.status_id = ds.id
                where dr.status_id = 2 or dr.status_id = 5 and d.site_id = 20
                group by dr.device_id

The table structure is

CREATE TABLE `deployment_register` (
  `id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `action_time` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Dumping data for table `deployment_register`
--

INSERT INTO `deployment_register` (`id`, `device_id`, `status_id`, `location_id`, `action_time`) VALUES
(1, 3, 2, 1, '2023-10-06 21:06:45'),
(7, 3, 5, 1, '2023-10-07 21:06:45');

ALTER TABLE `deployment_register`
  ADD PRIMARY KEY (`id`);

The site table

CREATE TABLE `event_site` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `notes` varchar(2000) NOT NULL,
  `job_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `event_site` (`id`, `name`, `notes`, `job_id`) VALUES
(1, 'qwef', 'efef', 19),
(2, 'This', 'Note', 20);

 

The query is selecting the first row when it should be selecting the second.

 

Any obvious issues here?

Edited October 7, 2023 by Adamhumbug

[Barand]

Are you saying you want the data for the latest row in the deployment_register table?

[Adamhumbug]

Yes, that matches the where clause of course.

[Barand]

Just using the single available table here...

SELECT dr.id
     , device_id
     , status_id
     , action_time
FROM deployment_register dr
     JOIN (
            SELECT device_id
                 , MAX(action_time) as action_time
            FROM deployment_register
            WHERE status_id IN (2, 5)
            GROUP BY device_id
          ) latest USING (device_id, action_time);

+----+-----------+-----------+---------------------+
| id | device_id | status_id | action_time         |
+----+-----------+-----------+---------------------+
|  7 |         3 |         5 | 2023-10-07 21:06:45 |
+----+-----------+-----------+---------------------+

Subquery to find latest row for each device then join against that.

[Barand]

P.S.

I forgot to explain that values in any columns that aren't aggregated (SUM, COUNT, MAX etc) or in the GROUP BY clause can come from any arbitrary record in the group (usually the first but no guarantees what the first will be without an ORDER  BY.

[Adamhumbug]

Thanks very much for this.  Where in here would the second part of the where clause go?  (The site_id = 20)

[Barand]

You need to join to the other tables to add that.

[Adamhumbug]

1 minute ago, Barand said:

You need to join to the other tables to add that.

I tried this - but it yields no results at all - doesnt error erither

SELECT dr.id, device_id, status_id, action_time, d.name as deviceName, d.type_id, d.notes, l.name, l.scanning_for, ds.name as deviceStatus
                from deployment_register dr
                inner join location l on dr.location_id = l.id
                inner join device d on dr.device_id = d.id
                inner join device_status ds on dr.status_id = ds.id
                JOIN (
                    SELECT device_id
                            , MAX(action_time) as action_time
                    FROM deployment_register
                    WHERE status_id IN (2, 5)
                    GROUP BY device_id
                    ) latest USING (device_id, action_time)
                where d.site_id = :site

 

[Barand]

9 minutes ago, Adamhumbug said:

I tried this - but it yields no results at all

I guess I'll have to take your word for that.

[Adamhumbug]

 WRONG SITE ID.

My error - thank you as always.

Edited October 8, 2023 by Adamhumbug

[Adamhumbug]

HI All,

Sorry, i have been workign with this query and its not actually doing what i need i dont think.

After correcting my wrong site faux pas - when using it, it seems to be showing me rows that it shouldnt.

SELECT dr.id, device_id, status_id, action_time, d.name as deviceName, d.type_id, d.notes, l.name, l.scanning_for, ds.name as deviceStatus
                from deployment_register dr
                inner join location l on dr.location_id = l.id
                inner join device d on dr.device_id = d.id
                inner join device_status ds on dr.status_id = ds.id
                JOIN (
                    SELECT device_id
                            , MAX(action_time) as action_time
                    FROM deployment_register
                    WHERE status_id IN (2, 5)
                    GROUP BY device_id
                    ) latest USING (device_id, action_time)
                where d.site_id = :site";

the register table

CREATE TABLE `deployment_register` (
  `id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `action_time` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


INSERT INTO `deployment_register` (`id`, `device_id`, `status_id`, `location_id`, `action_time`) VALUES
(1, 3, 2, 1, '2023-10-06 21:06:45'),
(7, 3, 5, 1, '2023-10-07 21:06:45'),
(8, 1, 5, 1, '2023-10-07 21:06:45'),
(9, 2, 2, 1, '2023-10-09 20:08:18'),
(10, 6, 2, 2, '2023-10-09 20:08:26'),
(11, 5, 2, 2, '2023-10-09 20:08:31'),
(12, 5, 2, 2, '2023-10-09 20:08:44'),
(17, 3, 3, 1, '2023-10-10 19:39:58'),
(18, 3, 3, 1, '2023-10-10 19:51:59');

ALTER TABLE `deployment_register`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `deployment_register`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
COMMIT;

as you can see id 18 has a status id of 3.  I only want to see the row that has the highest action_date where the status is 2,5.

It is not showing row 18 in the results of the query but insead row 7.

I have clearly got the query wrong and would really appreciate a pointer with it.

I hope i have explained this well enough, if not please shout and i will try and do a better job of explaining what i am trying to achieve.

Thanks as always.

Edited October 10, 2023 by Adamhumbug

[Barand]

What happens if you move the status condition to the outer where clause with the site condition?

[Adamhumbug]

5 minutes ago, Barand said:

What happens if you move the status condition to the outer where clause with the site condition?

❤️