AND syntax

Question

Post reply

AND syntax

Johnster

SSC Enthusiast

Points: 115
More actions
February 16, 2022 at 9:13 am

#3984251
Hello all, I am new to SQL, how to syntax 2 conditions connected with AND when trying to save value to variable?
When I use code bellow there is no error but also no value displayed.
```
SELECT @last_value = [VALUE] FROM dbo.TABLE_1 WHERE (IDMEASURE=(SELECT max(IDMEASURE)  FROM dbo.TABLE_1) AND IDRESOURCE=27)
PRINT @last_value
```

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

There is no value displayed because there are no results being returned from the query

If you run this, does it return anything?

SELECT [VALUE] FROM dbo.TABLE_1 WHERE (IDMEASURE=(SELECT max(IDMEASURE) FROM dbo.TABLE_1) AND IDRESOURCE=27)

It appears that this is what you may be looking for:

SELECT [VALUE], MAX(IDNMEASURE)
FROM TABLE_1 
WHERE IDRESOURCE=27
GROUP BY [VALUE]

Without seeing the schema and some sample data, this is a guess

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

To add to what Michael is saying, if you DO get results BUT the results are "NULL" (the literal NULL, not a string), then your variable will also come back with NULL as if it wasn't assigned. The other fun part about those types of queries is when you get multiple rows as a result, the variable can only hold 1 of those and it is (usually) the last one. BUT if you don't order your data (ORDER BY), the "last" one is not guaranteed to be the same with each execution of the query.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Mr. Brian Gale wrote:

To add to what Michael is saying, if you DO get results BUT the results are "NULL" (the literal NULL, not a string), then your variable will also come back with NULL as if it wasn't assigned. The other fun part about those types of queries is when you get multiple rows as a result, the variable can only hold 1 of those and it is (usually) the last one. BUT if you don't order your data (ORDER BY), the "last" one is not guaranteed to be the same with each execution of the query.

Great advice.

But, ORDER BY does not guarantee the results returned unless you have a TOP clause. I see this pattern frequently, and it's the source of a lot of "bugs".

Writing your query to return the single row you require would be preferred.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Johnster SSC Enthusiast Points: 115 More actions · Answer 4

Hello Michael, thanks for answer. If I run the query bellow the output is only column name [Value] and no value is displayed.

SELECT [VALUE] FROM dbo.TABLE_1 WHERE (IDMEASURE=(SELECT max(IDMEASURE) FROM dbo.TABLE_1) AND IDRESOURCE=27)

If I run this query the output is all rows from the table where IDRESOURCE=27 so this does not work neither.

SELECT [VALUE], MAX(IDNMEASURE)

FROM TABLE_1

WHERE IDRESOURCE=27

GROUP BY [VALUE]

Screen from my source table:

OPC_zdrojova_data

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

In your query, you are taking the MAX of IDMEASURE, not the MAX of IDMEASURE where the IDRESOURCE = 27.

Using your sample data, the MAX of IDMEASURE will be 21, which has an IDRESOURCE of 32. That will return no rows.

My query was a starting point meant to show you the data.

There are many ways to write this query, here is one:

WITH CTE
AS
(SELECT MAX(IDNMEASURE) MaxIDNMEASURE
FROM TABLE_1
WHERE IDRESOURCE=27)
SELECT T1.[VALUE] 
FROM TABLE_1 T1
WHERE EXISTS (SELECT * 
FROM CTE C
WHERE C.MaxIDNMEASURE = T1.IDNMEASURE)

Also, your object and column names leave a lot to be desired. VALUE is a key word. I hope Table_1 is only an example, not the name of an actual table.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kaj SSCommitted Points: 1605 More actions · Answer 6

Using your own syntax, the query could be:

SELECT [VALUE] 
FROM dbo.TABLE_1 t1
WHERE 
    IDMEASURE=(
        SELECT max(IDMEASURE) 
        FROM dbo.TABLE_1
        WHERE IDRESOURCE=t1.IDRESOURCE
        ) 
AND IDRESOURCE=27

See the added WHERE statement in the subselect?