SQL select query with multiple conditions issue

Question

I have a problem with a SQL select query, I can't figure out what it needs to be.

This is what my items table look like:

| id |   i_id   |      last_seen       |   spot    |
----------------------------------------------------
| 1  |   ls100  | 2017-03-10 15:30:40  |  spot800  |
| 2  |   ls100  | 2017-03-10 16:20:15  |  spot753  |
| 3  |   ls200  | 2017-03-10 16:33:10  |  spot800  |
| 4  |   ls300  | 2017-03-10 15:30:40  |  spot800  |
| 5  |   ls300  | 2017-03-10 12:10:30  |  spot800  |
| 6  |   ls400  | 2017-03-10 10:30:10  |  spot800  |

This is what I'm trying to obtain:

 | id |   i_id   |      last_seen       |   spot    |
 ----------------------------------------------------
 | 3  |   ls200  | 2017-03-10 16:33:10  |  spot800  |
 | 5  |   ls300  | 2017-03-10 12:10:30  |  spot800  |

So I need to have the rows where spot= 'spot800', last_seen = MAX(but only if the DateTime is the newest compared to all spots with the samei_id`), and at last the DateTime must be bigger than '2017-03-10 11:00:00'.

This is what I have so far:

SELECT * 
  FROM items
 WHERE spot = 'spot800' 
HAVING MAX(`last_seen`) 
   AND `last_seen` > '2017-03-10 11:00:00'

whether your query returning any records?

midhun k
– midhun k

2017-03-10 10:24:29 +00:00
Commented Mar 10, 2017 at 10:24 — midhun k
– midhun k, Commented Mar 10, 2017 at 10:24
You forgot grouping by i_id

Take_Care_
– Take_Care_

2017-03-10 10:25:38 +00:00
Commented Mar 10, 2017 at 10:25 — Take_Care_
– Take_Care_, Commented Mar 10, 2017 at 10:25

Strawberry · Accepted Answer · 2017-03-10 10:54:22Z

2

E.g.:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,i_id   INT NOT NULL
,last_seen       DATETIME NOT NULL
,spot    INT NOT NULL
);

INSERT INTO my_table VALUES
(1,100,'2017-03-10 15:30:40',800),
(2,100,'2017-03-10 14:20:15',753),
(3,200,'2017-03-10 16:33:10',800),
(4,300,'2017-03-10 15:30:40',800),
(5,300,'2017-03-10 12:10:30',800),
(6,400,'2017-03-10 10:30:10',800);

    SELECT [DISTINCT] x.* 
      FROM my_table x
      LEFT
      JOIN my_table y
        ON y.i_id = x.i_id
       AND y.last_seen < x.last_seen
     WHERE x.last_seen > '2017-03-10 11:00:00' 
       AND x.spot = 800
       AND y.id IS NULL;
----+------+---------------------+------+
| id | i_id | last_seen           | spot |
+----+------+---------------------+------+
|  3 |  200 | 2017-03-10 16:33:10 |  800 |
|  5 |  300 | 2017-03-10 12:10:30 |  800 |
+----+------+---------------------+------+
2 rows in set (0.00 sec)

answered Mar 10, 2017 at 10:54

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ser Akd Over a year ago

I still get id 1 returned and this one shouldn't be returned because its date is older than that from id 2

Ser Akd Over a year ago

Also the date in row 2 should be '2017-03-10 16:20:15'

Strawberry Over a year ago

@SerAkd You're welcome. You wanted rows with ids 3 & 5. It makes no sense (to me) to return a time and id that don't correspond.

Ser Akd Over a year ago

Indeed I wanted those rows, but when I execute the query it also returns id 1?

Strawberry Over a year ago

Plainly, that's not true. See above.

|

Matt · Accepted Answer · 2017-03-10 10:25:20Z

0

Use MAX and GROUP BY.

SELECT id, i_id, MAX(last_seen), spot  
FROM items
WHERE spot = 'spot800'
AND last_seen > '2017-03-10 11:00:00'
GROUP BY id, i_id, spot

answered Mar 10, 2017 at 10:25

Matt

15.1k28 gold badges109 silver badges161 bronze badges

3 Comments

Matt Over a year ago

because that matches your logic, id 1 is returned because it is the MAX last_seen for the ls100s

Ser Akd Over a year ago

My apologies, I forgot to edit it, id 1 shouldn't be the MAX last_seen

Ser Akd Over a year ago

If i use : GROUP BY i_id instead of GROUP BY id, i_id, spot then it returns the rows with id 1,3 and 5

Prefork · Accepted Answer · 2017-03-10 10:36:39Z

0

There is several things wrng with your statement.

Firstly, HAVING must be accompanied with a GROUP BY clause, so it's not what you are looking for.

Also, MAX is an aggregate, not a boolean, function. That is, it cannot be used in filters, such as a where clause or a having clause. Also, if it did work, MAX would only return the entry that contains the time as '2017-03-10 16:33:10'. Not what you expected.

Try this instead:

SELECT * FROM items WHERE (spot='spot800' AND last_seen > '2017-03-10 11:00:00');

answered Mar 10, 2017 at 10:36

Prefork

436 bronze badges

2 Comments

Strawberry Over a year ago

HAVING doesn't have to follow a GROUP BY clause - although that is its primary purpose. And your solution is incomplete, but otherwise, good.

Ser Akd Over a year ago

This query returns the rows with id 1,3,4,5 and it only needs to return the rows with id 3 and 5

Collectives™ on Stack Overflow

SQL select query with multiple conditions issue

3 Answers 3

9 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related