Error in mySQL command using 'GROUP BY' and 'WHERE ... IN' together

Question

I have a table with the following columns: DATE, TICKER, and ID. Essentially, what I am trying to do with the code below is select all of the ticker and its corresponding ID's (it can have multiple ID's). However, in the table, there are multiple entries where tickers can have the same ID, but fall under a different date, so in this case, I select the ID with the smallest date.

SELECT 
       MIN(DATE), 
       TICKER, 
       ID 
FROM Names 
GROUP BY 
        TICKER, 
        ID 
WHERE TICKER IN ('A', 'B')

Now, when I try the commands independently, it works, but I believe something goes wrong when I combine the GROUP BY and WHERE IN commands together, as I get a syntax error.

Let me know if anything isn't clear, and I will try to clarify it.

The same id, huh? So what's your PRIMARY KEY? – Strawberry May 23 '15 at 05:09 — Strawberry, May 23 '15 at 05:09

score 5 · Accepted Answer · answered May 23 '15 at 05:02

5

WHERE clause comes before GROUP BY clause does

SELECT MIN(DATE), TICKER, ID 
FROM Names 
WHERE TICKER IN ('A', 'B')
GROUP BY TICKER, ID

answered May 23 '15 at 05:02

Hanky Panky

46,730
8
72
95

score 1 · Answer 2 · edited May 23 '15 at 05:07

1

You are using GROUP BY clause before WHERE clause, maintain the clause sequences.

SELECT MIN(DATE), TICKER, ID 
FROM Names 
WHERE TICKER IN ('A', 'B')
GROUP BY TICKER, ID

edited May 23 '15 at 05:07

A l w a y s S u n n y

36,497
8
60
103

answered May 23 '15 at 05:03

Sachu

7,555
7
55
94

Thanks, works now, would you mind telling me about clause sequences, I've made some cursory looking over SQL, and never came across that. – StoneMan May 23 '15 at 05:13
you can check this [link](http://stackoverflow.com/questions/1130062/what-is-the-execution-sequence-of-group-by-having-and-where-clause-in-sql-server) – Sajan May 23 '15 at 05:16

score 1 · Answer 3 · answered May 23 '15 at 05:12

1

If there is no GROUP BY , WHERE IN will be in last but if GROUP BY is present then it should in last. use like this

SELECT MIN(DATE),
TICKER, ID 
FROM Names 
WHERE TICKER IN ('A', 'B')
GROUP BY TICKER, ID

answered May 23 '15 at 05:12

Sajan

1,893
2
19
39

oh, it seems you must have got that by now – Sajan May 23 '15 at 05:13

score 1 · Answer 4 · answered May 23 '15 at 05:34

Order of execution

When you execute a SQL Query, it will be executed in the following sequence.

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

TOP

This is the execution sequence

SELECT MIN(DATE),TICKER, ID 
FROM Names 
WHERE TICKER IN ('A', 'B')
GROUP BY TICKER, ID

For more information use Link

Error in mySQL command using 'GROUP BY' and 'WHERE ... IN' together

4 Answers4