CODEWITHSUNDEEP
mysqlsql
nenad007
nenad007

Reputation: 139

MySQL query aggregate MAX and subquery

Is there a way to get value from a subquery in where inside select?

stop_link Table

id | duid | domain_id 
1  | 1    | 1
2  | 2    | 1
3  | 1    | 2
4  | 2    | 2
5  | 3    | 1

Result that I want (assume domain_id = 2)

duid    | domain_id 
3       | 2

Query (not working):

INSERT INTO stop_link (
              duid,

              domain_id) 
SELECT
  IFNULL(MAX(sl.duid), 0) + 1 AS duid,
  sl.domain_id 
FROM
  stop_link sl
WHERE sl.domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1)

Query working but I wish to avoid the Set Variable:

SET @domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1);
SELECT
  IFNULL(MAX(sl.duid), 0) + 1 AS duid
  , @domain_id
FROM
  stop_link sl
WHERE sl.domain_id = @domain_id;

Upvotes: 0

Views: 76

Answers (3)

A C
A C

Reputation: 705

Changed answer based on new info from the comments. It sounds like you've got only_full_group_by enabled in your sql_mode. Your query would probably work with that disabled, but the following may also work for you:

INSERT INTO stop_link (
              duid,

              domain_id) 

SELECT
  IFNULL(MAX(sl.duid), 0) + 1 AS duid,
  sl.domain_id 
FROM
  stop_link sl
WHERE sl.domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1)
-- ORDER by sl.domain_id DESC LIMIT 1 # Removed in favor of the following line
GROUP BY sl.domain_id HAVING MAX(sl.id)
;

Note that the subquery may not be returning the stop_domain.id that you want it to be -- you might have intended to select MAX(sd.id), or perhaps you just removed a WHERE sd.url='foo' for clarity's sake.

Upvotes: 0

iMad
iMad

Reputation: 346

Do you mean something like this:

/*Your example Table*/

DECLARE @T  
TABLE(ID INT,duid INT,domain_id INT)

INSERT INTO @T
VALUES
(1 , 1    , 1 ),
(2  , 2    , 1),
(3  , 1    , 2),
(4  , 2    , 2),
(5  , 3    , 1)

--The query
SELECT domain_id,Isnull(max(duid),0)+1 [newId]
FROM @T
GROUP BY domain_id

Upvotes: 1

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

No need of max():

SELECT
  IFNULL(sl.duid, 0) + 1 AS duid,
  sl.domain_id 
FROM
  stop_link sl
WHERE sl.domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1)
ORDER by sl.id DESC
LIMIT 1

Upvotes: 0

Related Questions