CODEWITHSUNDEEP
sql
Steffan Harris
Steffan Harris

Reputation: 9326

Incorrect Syntax near Keyword Group

Hello I'm writing an sql query But i am getting a syntax error on the line with the GROUP BY. What can possibly be the problem.

SELECT au_lname, au_fname, t.title_id
from authors As a INNER JOIN
titleauthor As ta On a.au_id = ta.au_id INNER JOIN
titles As t On t.title_id = ta.title_id
ORDER BY au_lname, au_fname
GROUP BY au_lname

Upvotes: 2

Views: 37093

Answers (2)

Tassadaque
Tassadaque

Reputation: 8199

General syntax for select statement is 

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ;

so in your case

SELECT au_lname, au_fname, t.title_id
from authors As a INNER JOIN
titleauthor As ta On a.au_id = ta.au_id INNER JOIN
titles As t On t.title_id = ta.title_id
GROUP BY au_lname
ORDER BY au_lname, au_fname

Upvotes: 0

Tommi
Tommi

Reputation: 8608

Place GROUP BY before ORDER BY. Also, all fields included in the select must either be in the GROUP BY statement, or be inside an aggregate function.

Try this:

SELECT au_lname, au_fname, t.title_id
from authors As a INNER JOIN
titleauthor As ta On a.au_id = ta.au_id INNER JOIN
titles As t On t.title_id = ta.title_id
GROUP BY au_lname, au_fname, t.title_id
ORDER BY au_lname, au_fname

If you just want to get rid of duplicate results, you can also use keyword DISTINCT:

SELECT DISTINCT au_lname, au_fname, t.title_id
from authors As a INNER JOIN
titleauthor As ta On a.au_id = ta.au_id INNER JOIN
titles As t On t.title_id = ta.title_id
ORDER BY au_lname, au_fname

Upvotes: 7

Related Questions