Menu
What am I doing wrong here? I am getting this error on:
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 would like to group by the first, second, and third columns having the max rid.
It works fine without the group by and having.
842
Description of an outer reference An outer reference is a column name that does not refer to any of the columns in any of the tables in the FROM clause of the subquery. Instead, the column name refers to a column of a table specified in the FROM clause of the main query.
The SQL GROUP BY StatementThe GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic.
To start with you can't 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 that's only valid in ORDER BY.
Can you explain why this isn't 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 can't 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
