• Register
0 votes
183 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 22 views
22 views
Problem: What is the difference between the left join and the left outer join?
asked Feb 27 sasha 16.2k points
0 votes
1 answer 13 views
0 votes
1 answer 13 views
13 views
Problem: I need help in resolving this problem: Which type of join returns rows from one table that have no direct match in the other table?
asked Apr 1 rakib1 51.5k points
0 votes
1 answer 264 views
264 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
1 answer 30 views
30 views
Problem column name or number of supplied values does not match table definition
asked Feb 9 charles mathews 5.5k points
1 vote
1 answer 18 views
18 views
Problem : Can someone please help, I am new in SQL Query. I have a table Where I want to find highest salary of employee in each department .
asked Mar 3 Sabbir Hossain 14.6k points
0 votes
1 answer 2 views
2 views
Problem: Please assist me in resolving this issue: Return all rows when there is a match in one of the tables.
asked Mar 29 tuhin1 48.9k points
0 votes
1 answer 7 views
7 views
Problem: How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
asked Apr 19 ummesalma 29.2k points
0 votes
1 answer 18 views
18 views
Problem: sql select top 1 from each group. how to solve this problem.
asked Mar 13 Dan phillip 4.8k points
0 votes
1 answer 4 views
4 views
Problem: I did a select * on a table. I exported the results and tried to import it and write to another table (I have to do this via ssis, I cannot do this via sql server (i.e. select into) for security permission purposes, but I can do it this way). How can I ... to use an ole db source step, but my server instance does not appear for some reason, so now I'm trying to export/import out of SSMS.
asked Apr 25 sumaiya simi 43.9k points