CODEWITHSUNDEEP
mysqlnode.js
Tabam
Tabam

Reputation: 115

NodeJS, MySQL - Advanced query

"Test" table structure

I want to check if in table "Test" there is an result with itemID = '123' and value = '456' and whether it is the last added result ORDER BY id DESC, I miss something in this code:

SELECT * FROM Test WHERE itemID = '123' AND value= '456' ORDER BY id DESC LIMIT 1

Could anyone help?

Upvotes: 0

Views: 169

Answers (3)

Michael - sqlbot
Michael - sqlbot

Reputation: 179004

SELECT (SELECT value 
          FROM Test 
         WHERE itemID = '123' 
         ORDER BY id DESC LIMIT 1) = '456' AS it_matches;

The result will be one of these possibilities:

  • 1 if the last "value" is 456, or
  • 0 if the last "value" is another non-null value, or
  • NULL if there are no rows with ItemID = 123 or the last row's "value" column is null.

Upvotes: 0

smita
smita

Reputation: 329

Using Sub query in where clause you can find it.

 SELECT t.* FROM `Test` as t WHERE `itemID` = 123 AND `value` = 456 AND `id` =(SELECT max(`id`) FROM Test);

Upvotes: 0

GMB
GMB

Reputation: 222432

check if in table Test there is an result with itemID = '123' and value = '456' and whether it is the last added result ORDER BY id DESC

Your requirement can be litteraly translated as follows:

select *
from test t
where itemID = 123 and value = 456
and not exists (
    select 1
    from test t1
    where t1.id > t.id
)

The NOT EXISTS condition ensures that the record being selected is the latest, id-wise.

If the requirements are not satisfied, the query returns an empty resultset.

Another way to express it is to use a correlated subquery to get the latest id:

select *
from test t
where 
    itemID = 123 
    and value = 456
    and id = (select max(id) from test t)

Upvotes: 2

Related Questions