• Register
0 votes
135 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.

6 5 3
7,540 points

Please log in or register to answer this question.

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

9 7 4
38,600 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.

10 6 4
31,120 points

Related questions

0 votes
1 answer 10 views
0 votes
1 answer 213 views
213 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
0 answers 6 views
6 views
Problem: What is the difference between the left join and the left outer join?
asked 3 days ago sasha 13.2k points
0 votes
1 answer 15 views
15 views
Problem column name or number of supplied values does not match table definition
asked Feb 9 charles mathews 3.8k points
0 votes
1 answer 19 views
0 votes
2 answers 651 views
651 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 3 views
0 votes
1 answer 57 views
57 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 42 views
42 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, 2020 mphil 2.3k points