Writing MSSQL (2019) query equivalent to MySQL query

Question

I have written the following MySQL query in my project to select all tests table record with matching test_change table record (whichever is the first) for a data display purpose.

I am in need of the MSSQL equivalent query but am unable to form the query because of my limited knowledge in it. I tried the website http://www.sqlines.com/online but I did not help me.

This is the MySQL Query:

SELECT
  tests.*,
  cases.title,
  users.name,
  statuses.label as status_label,
  statuses.color_dark,
  tc.id as change_id,
  tc.created_on
FROM
  tests
  left join (
    select
      MIN(created_on) as created_on,
      test_id,
      id,
      assignedto_id
    from
      test_changes
    group by
      test_id
  ) tc on tests.id = tc.test_id
  LEFT JOIN users ON tc.assignedto_id = users.id
  LEFT JOIN cases ON tests.case_id = cases.id
  LEFT JOIN statuses ON tests.status_id = statuses.id
WHERE
  tests.id is not null
  AND tests.run_id IN (22)
  AND (
    tests.status_id = 3
    or tests.status_id = 4
    or (
      tests.status_id != 3
      and tc.created_on > 1620950399
    )
  )
GROUP BY
  tests.id
ORDER BY
  users.name DESC
LIMIT
  15, 20

This is the MSSQL Query I tried...

SELECT
  tests.*,
  cases.title,
  users.name,
  statuses.label as status_label,
  statuses.color_dark,
  tc.id as change_id,
  tc.created_on
FROM
  tests
  left join (
    select
      MIN(created_on) as created_on,
      status_id,
      test_id,
      id,
      assignedto_id
    from
      test_changes
    group by
      test_id
  ) tc on tests.id = tc.test_id
  LEFT JOIN users ON tc.assignedto_id = users.id
  LEFT JOIN cases ON tests.case_id = cases.id
  LEFT JOIN statuses ON tests.status_id = statuses.id
WHERE
  tests.id is not null
  AND tests.run_id IN (22)
  AND (
    tests.status_id = 3
    or tests.status_id = 4
    or (
      tests.status_id != 3
      and tc.created_on > 1620950399
    )
  )
GROUP BY
  tests.id
ORDER BY
  users.name DESC OFFSET 15 ROWS FETCH NEXT 20 ROWS ONLY

It is throwing the following error...

Column 'test_changes.status_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone help me in resolving the error and forming this MSSQL query?

Please provide sample data, desired results, and an explanation of what the query is supposed to be doing. — Gordon Linoff, May 15 '21 at 18:26
Your MySQL query is errorneous. But MySQL extension allows to execute such wrong queries... Investigate each separate output column - if it has all values in a group identical then add it into GROUP BY, else either use some aggregate function over it (for example, MIN) or remove it from output list at all. — Akina, May 15 '21 at 18:39
Does this answer your question? [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) — Charlieface, May 15 '21 at 22:10

score 1 · Answer 1 · answered May 15 '21 at 18:37

1

even your first query in mysql gives you the same error , you can't select columns that are not aggregated or part of group by , when you group by.

so seems like you nmeed to group by assignedto_id and test_id as well:

     select
            MIN(created_on) as created_on,
            status_id,
            test_id,
            --id,  <-- removed this column , looks not used in query
            assignedto_id
        from
            test_changes
        group by
            test_id, status_id,assignedto_id -- < adding new columns to group by
) tc on ....

it might not be what you are looking for , but gives you the idea how it works

answered May 15 '21 at 18:37

eshirvana

23,227
3
22
38

With MySQL it depends on the version and compatibility settings whether or not the query produces an error. – Mark Rotteveel May 15 '21 at 18:57
@MarkRotteveel right , nevertheless it is wrong – eshirvana May 15 '21 at 18:58
2

This is the correct answer. MySQL can permit a very dangerous habit of users not being forced to appropriately group the fields they aren't otherwise aggregating. SQL Server does not permit this (rightly so). In general, try running your subqueries as standalone queries when trying to diagnose a problem like this. – Tom Renish May 15 '21 at 23:10
Thank you @eshirvana for your help. this does helped me to rewrite in different way. – Joseph May 24 '21 at 14:41

Writing MSSQL (2019) query equivalent to MySQL query

1 Answers1