Each group by expression must contain at least one column that is not an outer reference.

Question

Problem :

I am new to the SQL. What am I doing wrong here?

SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),

PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%',batchinfo.datapath), 8000))-1),qvalues.name,qvalues.compound,qvalues.rid

FROM batchinfo JOIN qvalues ON batchinfo.rowid=qvalues.rowid

WHERE LEN(datapath)>4

GROUP BY 1,2,3

HAVING rid!=MAX(rid)

I am getting error when I try to execute above query.

I am trying to do group by the first, second, and third columns having the max rid.

It is works perfectly fine without the group by and having.

Answer 1

I have lots of experience in writing complex SQL Server queries.

I have gone through your post and understood your problem.

So to begin with you must not do the following mistake:

having rid!=MAX(rid)

The HAVING clause which you want to use can only contain things which are attributes of the aggregate groups.

In addition to that, 1, 2, 3 are not valid in GROUP BY in SQL Server .I think those are only valid in ORDER BY clause.

Please find the following updated code which will work and solve your issues.

select

LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),

qvalues.name,

qvalues.compound,

MAX(qvalues.rid)

 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid

where LEN(datapath)>4

group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),

qvalues.name,

qvalues.compound

Answer 2

To begin with you can't do this:

having rid!=MAX(rid)

The Having clause can just hold things which are attributes of the aggregate groups.

Moreover, 1, 2, 3 is not valid in GROUP BY in SQL Server - I think that's just valid in ORDER BY.

Can you interpret why this isn't what you are search for:

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid)
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

Well, as it was expressed prior, you can't GROUP by literals, I think that you are confused because you can ORDER by 1, 2, 3. at the time you employ functions as your columns, you require to GROUP by the similar expression. Yet, the HAVING clause is wrong, you can just exercise what is in the agreggations. In this instance, your query must be like this:

SELECT 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid) MaxRid
FROM batchinfo join qvalues 
ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

You can't group by literals, just columns.

You are perhaps searching for something like th

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1) as pathinfo,
qvalues.name,
qvalues.compound,
qvalues.rid
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by pathinfo, qvalues.name, qvalues.compound
having rid!=MAX(rid)

Firstly, you have to give that first expression a column name with as. Then you have to fix the names of the columns in the group by expression.