[RESOLVED] SQl problem

[chunk]

hi all im using this code, and it is working very fine and my reports shows the expected result

If cmb.ListIndex = 1 Then
Set rs = con.Execute("select marks from StuMarks where srollno=21")
Set DataReport1.DataSource = rs
DataReport1.Show
End If

but i have to change the to this one

Set rs = con.Execute("select MAX(marks) from StuMarks where srollno=21")

this is not working i get an error when report opens.

Data field 'marks' not found

but same SQL works without MAX function

please solve this

[RobDog888]

Try...

Set rs = con.Execute("select MAX(marks) AS marks from StuMarks where srollno=21")

[chunk]

Great
thank you so much Rob

[RobDog888]

No prob. Glad to help

Ps, sorry for the delay

[chunk]

No prob. Glad to help

Ps, sorry for the delay

hehehe
there was no delay, you replied this and solved this problem within One minute

[RobDog888]

I know, was making fun at the instant response.


Oh and btw the error was because when you use a calculation function like that it will change the field name as a result of its calculation like "MaxOfmarks" or such. So by aliasing it back to the original it will be found.

[chunk]

yeah thats y i wrote hehehe in my previous post. please now don't make this thread up

[chunk]

oh please I'm sorry
please tell me whats wrong in this query

Set rs = con.Execute("select name, MAX(marks) AS marks from StuMarks where srollno=21")

[RobDog888]

Well when you use an aggrete function like MAX any other fields you include in the select list need to either be having some other aggrete function applied to it or just be included in a GROUP BY clause at the end of the query.

Set rs = con.Execute("select [name], MAX(marks) AS marks from StuMarks where srollno=21 GROUP BY [name]")

[chunk]

my actual query is

Set rs = con.Execute("select [rollno], MAX(markoptained) as markoptained from markccc WHERE [session]= " & Text1.Text & "")

and i get this error

you tried to execute a query that does not include the specified expression ' rollno' as part of an aggregate function

[RobDog888]

Yup and you didnt add the rest of what I had wrote about the GROUP BY clause

[chunk]

can u pls tell me what to do next in this query?

[chunk]

it is showing now two records Rob
and i want to show only one record

Set rs = con.Execute("select [rollno], MAX(markoptained) as markoptained from markccc WHERE [session]= " & Text1.Text & " Group by rollno")

[RobDog888]

Is "rollno" unique between the two or are they the same. Can you post some example record data?

[chunk]

i have wrote the whole code in my #1 post
and my table have only these three fields only
rollno
markoptained
session

[RobDog888]

But you are not adding the extra fields to your select list as I have been mentioning like in post #9.

For your other fields it would look like this.

Set rs = con.Execute("select rollno, markoptained, session, MAX(marks) AS marks from StuMarks where srollno=21 GROUP BY rollno, markoptained, session")

[chunk]

it is still showing me Two Records Rob

Set rs = con.Execute("select rollno, markoptained, [session], MAX(markoptained) AS markoptained from markccc where [session]= " & Text1.Text & " GROUP BY rollno, markoptained, [session]")

[chunk]

not only two records,
it is showing all the records which fulfils the criteria

[RobDog888]

Well Im not sure what the reason is as I cant see your resulting data. If its sensitive just post a relevant mock up. the query is right but without the data it is not known if its possible at all.

[chunk]

Rob,
im upload the example, please click the button Report. when you run this example.
i want only one record (MAX marks) to be shown but it is showing all the records

[RobDog888]

Ok since you have the rollno field in there it is creating the multiple records because it cant be grouped by because there are no common values to group on. How are you going to group "4, 5, 7, 8"?

[chunk]

i just want to show the MAX marks and the rollno of its row.
this is the requirement

[RobDog888]

Im not sure what your rollno field is used for but if you have to show it you can show just the FIRST or LAST value as those are aggregete functions.

This will give you one record as I think you may need.

Set rs = con.Execute("select FIRST(rollno) AS rollno, MAX(markoptained) AS markoptained from markccc where [session]= " & Text1.Text)

[RobDog888]

i just want to show the MAX marks and the rollno of its row.
this is the requirement

You can not show just one row with the rollno field as I have previously posted the values in that field are not all the same so there is nothing to group them by when MAX the other field.

What is it supossed to look like?

4 - 425
5 - 425
7 - 425
8 - 425

????

You can not do that with a single sql call.

[chunk]

now this problem is solved.
hehehe
Thank you so much Rob

[chunk]

no not working,
RollNo is not coming of the particular Row.

[RobDog888]

Can you please post exactly how the record should look? Only you know how you are needing it.

[chunk]

i want to show the Tooper(S) marks of Student in the report and his Roll No. only

[RobDog888]

What does that mean? Can you just post how the complete data should look?

rollno  |  markoptained
----------------------
4          425

Something like that.

[chunk]

rollno markoptained session
4 299 201
5 395 201
6 465 205
7 380 201
8 258 201

this is how data is saved in the table, the highest marks are 465. and output should be like

rollno | markoptained
----------------------
6 465

because corrosponding rollno is 6

[leinad31]

It would be best if you learned other features of SQL yourself. http://sqlzoo.net/

[RobDog888]

Ok, then that is easier as we can simply sort the table in descending order and limit its return to a single record which will be the maximum value for markoptianed.

Set rs = con.Execute("SELECT TOP 1 rollno, markoptained from markccc ORDER BY markoptained DESC")

[RobDog888]

It would be best if you learned other features of SQL yourself. http://sqlzoo.net/

Most of it is just getting the communications correct. Having an example of how you want/need the results show alot more then one would think.

[leinad31]

Most of it is just getting the communications correct. Having an example of how you want/need the results show alot more then one would think.

For one problem, yes. But in the long run I lean towards "experience is the best teacher" mentality... examples are harder to understand without knowledge of the fundamentals, e.g. discussion on GROUP BY above.

[RobDog888]

Well without going off topic, we can help best by showing them the correct path instead of posting a generic link and have them be totally lost.

[leinad31]

Well without going off topic, we can help best by showing them the correct path instead of posting a generic link and have them be totally lost.

I trust in his ability to decide which of the topics covered are relevant to his immediate and future needs.

[chunk]

now this is completly Resolved Rob

[chunk]

Rob i'm so sorry,
actully im too much disturb these days because i lost my girl friend, and she is going to marry some other guy, and my mind is not working properly. please forgive me because im making this thread up again

please tell me what should be done in SQL, if there are two or more student who has same Top marks.

[leinad31]

please tell me what should be done in SQL, if there are two or more student who has same Top marks.

Largely depends on how you phrased your SQL, what you want to accomplish, and how your tables are structured... it's possible to return the tied records, its also possible to return just one (without going into technical details, just imagine that choice of record is up to the database), it may return an error in some joins or produce a cartesian result...

So you see, rather than asking for help for each and every query, step back and familiarize yourself with the fundamentals.