MySQL: Select newest two rows per Group

I have a table like this:

CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `deviceId` int(11) NOT NULL,
  `position_x` int(11) NOT NULL,
  `position_y` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `data`
  ADD PRIMARY KEY (`id`);
COMMIT;

id, deviceId, position_x, position_y
1   1           100         0  
2   2           150         50
3   3           200         20 
4   1           220         20
5   1           210         10
6   2           100         40
7   3           120         50
8   3           130         60
9   2           240         15

I need the “newest” two rows per DeviceID, where a bigger ID means newer. Right now, I’m selecting the newest row per Device via this query:

SELECT
    id,
    deviceId,
    position_x, position_y
FROM data
WHERE deviceId > 0 AND
  id IN (SELECT MAX(id) FROM data GROUP BY deviceId)

And in a loop, where I output the data, I select the second latest row for every deviceId in an individual query, which is kinda slow/dirty:

SELECT
    position_x
    position_y
FROM data
WHERE deviceId = :deviceId AND
    id < :id
ORDER BY id DESC
LIMIT 1

Is there a way to combine both queries or at least, in one query, select the second row for every deviceId from query 1?

Thanks

Answer

You can try using row_number()

select * from
(
SELECT
    id,
    deviceId,
    position_x, position_y,row_number() over(partition by deviceid order by id desc) as rn
FROM data
WHERE deviceId > 0
)A where rn=2

Advertisement

Answer