-1

I'm trying to do something like this one SQL query to get most recent row for each instance of a given key

but instead of using the max(created_at), I'm supplying a certain created_at value that I will use in my query.

Here is my sample data. I have 40 unique node_ids

+---------+---------------------+
| node_id | created_at          |
+---------+---------------------+
|      26 | 2017-01-05 17:35:14 |
|       5 | 2017-01-05 14:58:16 |
|      22 | 2017-01-05 14:58:16 |
|      20 | 2017-01-05 14:58:16 |
|      15 | 2017-01-05 14:58:16 |
|      12 | 2017-01-05 14:58:16 |
|       8 | 2017-01-05 14:58:16 |
|      11 | 2017-01-05 14:58:16 |
|      33 | 2017-01-05 14:58:16 |
|      23 | 2017-01-05 14:58:16 |
|       4 | 2017-01-05 14:58:16 |
|      39 | 2017-01-05 14:57:30 |
|      30 | 2017-01-05 14:57:30 |
|      26 | 2017-01-05 14:57:30 |
|      17 | 2017-01-05 14:57:30 |
|      16 | 2017-01-05 14:57:30 |
|      37 | 2017-01-05 14:42:42 |
|      35 | 2017-01-05 14:42:42 |
|      13 | 2017-01-05 14:42:42 |
|      21 | 2017-01-05 14:42:20 |
|      25 | 2017-01-05 14:42:20 |
|      14 | 2017-01-05 14:42:20 |
|       9 | 2017-01-05 14:42:20 |
|       2 | 2017-01-05 14:42:20 |
|      38 | 2017-01-05 14:41:36 |
|      29 | 2017-01-05 14:41:36 |
|      28 | 2017-01-05 14:41:36 |
|      24 | 2017-01-05 14:41:36 |
|      18 | 2017-01-05 14:41:36 |
|       7 | 2017-01-05 14:41:36 |
|       1 | 2017-01-05 14:41:36 |
|      32 | 2017-01-05 14:41:36 |
|      10 | 2017-01-05 14:41:36 |
|      36 | 2017-01-05 14:41:36 |
|      19 | 2017-01-05 14:40:47 |
|      27 | 2017-01-05 14:40:47 |
|      31 | 2017-01-05 14:40:47 |
|      34 | 2017-01-05 14:40:47 |
|      35 | 2017-01-05 14:40:47 |
|      37 | 2017-01-05 14:40:47 |
|      40 | 2017-01-05 14:40:47 |
|       3 | 2017-01-05 14:40:47 |
|       6 | 2017-01-05 14:40:47 |
|      13 | 2017-01-05 14:40:21 |
|       1 | 2017-01-05 14:40:09 |
|       2 | 2017-01-05 14:40:09 |
|       3 | 2017-01-05 14:40:09 |
|       4 | 2017-01-05 14:40:09 |
|       5 | 2017-01-05 14:40:09 |
|       6 | 2017-01-05 14:40:09 |
|       7 | 2017-01-05 14:40:09 |
|      40 | 2017-01-05 14:40:09 |
|      33 | 2017-01-05 14:40:09 |
|      39 | 2017-01-05 14:40:09 |
|      38 | 2017-01-05 14:40:09 |
|      37 | 2017-01-05 14:40:09 |
|      36 | 2017-01-05 14:40:09 |
|      35 | 2017-01-05 14:40:09 |
|      34 | 2017-01-05 14:40:09 |
|      32 | 2017-01-05 14:40:09 |
|      31 | 2017-01-05 14:40:09 |
|      30 | 2017-01-05 14:40:09 |
|      29 | 2017-01-05 14:40:09 |
|      28 | 2017-01-05 14:40:09 |
|      27 | 2017-01-05 14:40:09 |
|      26 | 2017-01-05 14:40:09 |
|      25 | 2017-01-05 14:40:09 |
|      24 | 2017-01-05 14:40:09 |
|      23 | 2017-01-05 14:40:09 |
|      22 | 2017-01-05 14:40:09 |
|      21 | 2017-01-05 14:40:09 |
|      20 | 2017-01-05 14:40:09 |
|      19 | 2017-01-05 14:40:09 |
|      11 | 2017-01-05 14:40:09 |
|      18 | 2017-01-05 14:40:09 |
|      17 | 2017-01-05 14:40:09 |
|      16 | 2017-01-05 14:40:09 |
|      15 | 2017-01-05 14:40:09 |
|      14 | 2017-01-05 14:40:09 |
|      13 | 2017-01-05 14:40:09 |
|      12 | 2017-01-05 14:40:09 |
|      10 | 2017-01-05 14:40:09 |
|       9 | 2017-01-05 14:40:09 |
|       8 | 2017-01-05 14:40:09 |
+---------+---------------------+

The query that I used here was:

select node_id, created_at from nodes_position where created_at <= '2017-01-05 17:35:14' order by created_at desc;

What I would like to do is to get only the rows with closest created_at to the one that I supplied and also with uniquer node_id.

The result that I should have is something like this (with only 40 rows since there are only 40 unique node_ids)

+---------+---------------------+
| node_id | created_at          |
+---------+---------------------+
|      26 | 2017-01-05 17:35:14 |
|       5 | 2017-01-05 14:58:16 |
|      22 | 2017-01-05 14:58:16 |
|      20 | 2017-01-05 14:58:16 |
|      15 | 2017-01-05 14:58:16 |
|      12 | 2017-01-05 14:58:16 |
|       8 | 2017-01-05 14:58:16 |
|      11 | 2017-01-05 14:58:16 |
|      33 | 2017-01-05 14:58:16 |
|      23 | 2017-01-05 14:58:16 |
|       4 | 2017-01-05 14:58:16 |
|      39 | 2017-01-05 14:57:30 |
|      30 | 2017-01-05 14:57:30 |
|      17 | 2017-01-05 14:57:30 |
|      16 | 2017-01-05 14:57:30 |
|      37 | 2017-01-05 14:42:42 |
|      35 | 2017-01-05 14:42:42 |
|      13 | 2017-01-05 14:42:42 |
|      21 | 2017-01-05 14:42:20 |
|      25 | 2017-01-05 14:42:20 |
|      14 | 2017-01-05 14:42:20 |
|       9 | 2017-01-05 14:42:20 |
|       2 | 2017-01-05 14:42:20 |
|      38 | 2017-01-05 14:41:36 |
|      29 | 2017-01-05 14:41:36 |
|      28 | 2017-01-05 14:41:36 |
|      24 | 2017-01-05 14:41:36 |
|      18 | 2017-01-05 14:41:36 |
|       7 | 2017-01-05 14:41:36 |
|       1 | 2017-01-05 14:41:36 |
|      32 | 2017-01-05 14:41:36 |
|      10 | 2017-01-05 14:41:36 |
|      36 | 2017-01-05 14:41:36 |
|      19 | 2017-01-05 14:40:47 |
|      27 | 2017-01-05 14:40:47 |
|      31 | 2017-01-05 14:40:47 |
|      34 | 2017-01-05 14:40:47 |
|      35 | 2017-01-05 14:40:47 |
|      37 | 2017-01-05 14:40:47 |
|      40 | 2017-01-05 14:40:47 |
|       3 | 2017-01-05 14:40:47 |
|       6 | 2017-01-05 14:40:47 |
|      13 | 2017-01-05 14:40:21 |
+---------+---------------------+

How can this be done? Thanks.

Community
  • 1
  • 1
Mahrrkiee
  • 35
  • 7
  • Can you provide us some details about your issue? Things like table definition, sample data and desired output? Also you should tag this with the relevant DBMS you are actually working with. – Sean Lange Jan 06 '17 at 14:46
  • also show what you have done to research this question. There are countless tutorials on the internet already that show how to use parameters in SQL queries. Which one did you read, and why didn't it work for you? – Tab Alleman Jan 06 '17 at 14:47

2 Answers2

0

Fundamentally here, you're looking for the minimum time difference...

select *
from nodes_position  a1
where abs(timediff(@my_time, a1.created_at)) 
      = (select min(abs(timediff(@my_time,np.created_at))) 
         from nodes_position np)
JohnHC
  • 10,935
  • 1
  • 24
  • 40
0

Did you mean this?

SELECT node_id, MAX(created_at) FROM nodes_position WHERE created_at <= '2017-01-05 17:35:14' GROUP BY node_id ORDER BY node_id;

You can also ORDER BY 2 to sort by created_at.

Here GROUP BY node_id will group together all rows with the same node_id from 

node_id | created_at
   5    | 2000-01-01
   5    | 2222-01-01
   7    | 3333-01-01

into 

node_id | created_at
   5    | { 2000-01-01, 2222-01-01 }  <-- MAX({ 2000-01-01, 2222-01-01 }) = 2222-01-01
   7    | { 3333-01-01 }

and the WHERE will only allow rows to participate in GROUPing which are equal or lower created_at.

flutter
  • 694
  • 3
  • 8