CODEWITHSUNDEEP
sqlmariadb
user8280493
user8280493

Reputation:

If conditions in SQL

Clarification. I want to have a query that finds me the row with newest Timestamp, but if that row has column deleted set to 1 then I want to get back NULL. That is what I am trying to do.

I would like to make a query with sub query in WHERE clause something like:

SELECT *
FROM table
WHERE id = (SELECT id FROM table WHERE ts > 'x' ORDER BY ts LIMIT 1)

But the thing is there is a column called DELETED in table, and if DELETED = 1 I would like to get just this

SELECT *
FROM table 
WHERE id = NULL

How could I do that?

I am working with MariaDB, but any SQL would help.

Upvotes: 0

Views: 109

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I don't think this is related to your question, but why not just execute the query as:

SELECT t.*
FROM table t
WHERE t.ts > 'x'
ORDER BY t.ts
LIMIT 1;

It seems simpler than your version of the query -- assuming that id is unique in the table.

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

Check this one:

SELECT *
FROM table
WHERE id = (SELECT id FROM table WHERE ts > 'x' ORDER BY ts LIMIT 1)
AND deleted = 0;

Upvotes: 0

Rainer Feike
Rainer Feike

Reputation: 191

I understand that you want the "DELETED" result if there is any. Else you want the row with a ts greater than x. Assuming that "DELETED is 0 if not 1, you can use Gordons SQL like this:

SELECT t.*
FROM table t
WHERE (t.ts > 'x' or t.deleted = 1)
ORDER BY t.deleted desc, t.ts
LIMIT 1;

Upvotes: 1

Related Questions