sql server – Each GROUP BY expression must contain at least one column that is not an outer reference

sql server – Each GROUP BY expression must contain at least one column that is not an outer reference

To start with you cant do this:

having rid!=MAX(rid)

The HAVING clause can only contain things which are attributes of the aggregate groups.

In addition, 1, 2, 3 is not valid in GROUP BY in SQL Server – I think thats only valid in ORDER BY.

Can you explain why this isnt what you are looking 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 said before, you cant GROUP by literals, I think that you are confused cause you can ORDER by 1, 2, 3. When you use functions as your columns, you need to GROUP by the same expression. Besides, the HAVING clause is wrong, you can only use what is in the agreggations. In this case, your query should 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

sql server – Each GROUP BY expression must contain at least one column that is not an outer reference

You cant group by literals, only columns.

You are probably looking for something 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) 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)

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