• Register
search
Log In
0 votes
58 views

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.

7.5k points

2 Answers

0 votes

Solution :

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

36.1k points
0 votes

Solution:

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.

12.8k points
edited by

Related questions

0 votes
1 answer 102 views
102 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
2 answers 87 views
87 views
Problem : I want to import the flat file into my oledb target sql server database. But I am getting below error message : [Source - 18942979103_txt [424]] Error: Data conversion failed. The data conversion for column "recipient-name" returned status value 4 and status text ... more characters had no match in the target code page.". Why am I getting this error message? What am I doing wrong here?
asked Dec 23, 2019 alecxe 7.5k points
0 votes
1 answer 24 views
24 views
Problem : I was getting the following error when running a SQL the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
asked Nov 12, 2019 peterlaw 6.9k points
0 votes
1 answer 16 views
16 views
Problem : Currently I am using the MySQL 5.7.13 on the windows PC with the WAMP Server While executing below query I am facing following error SELECT * FROM `tbl_customer_pod_uploads` WHERE `load_id` = '78' AND `status` = ' ... column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
asked Feb 18 mphil 2.3k points
0 votes
1 answer 29 views
29 views
Problem : I am currently using MySQL 5.7.13 on my windows PC with the WAMP Server I am facing following error while executing below the query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ' ... functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Does anybody have any solution for above error?
asked Nov 29, 2019 alecxe 7.5k points