CODEWITHSUNDEEP
mysqlsql
tim
tim

Reputation: 10186

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

Upvotes: 1

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use a correlated subquery for this as well:

SELECT d.*
FROM data d
WHERE d.deviceId > 0 AND
      d.id = (SELECT d2.id
              FROM data d2
              WHERE d2.deviceId = d.deviceId
              ORDER BY d2.id DESC
              LIMIT 1, 1
             );

With an index on data(deviceId, id desc), you might be impressed at the performance.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 2

Related Questions