ASP SQL statement

I have an asp form that does a query using SQL (sql server/database)

The query currently retrieves the highest value using "select max".
Is there any way to retrieve the highest two values like (select max and select max -1) ?

here is my statement

objRS.Open "Select max(BldLabel) as BldLabel from cmpsub join cmpmap on cmpmap.multisub = cmpsub.cmpname and cmpmap.subcode = cmpsub.codeline where cmpmap.codeline =  '" & strOS & "'GROUP by left(BldLabel,charindex('_',BldLabel)-1)" ,objConnection,3,3    
tmurray22Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
objRS.Open "Select top 2 BldLabel from cmpsub join cmpmap on cmpmap.multisub = cmpsub.cmpname and cmpmap.subcode = cmpsub.codeline where cmpmap.codeline =  '" & strOS & "'GROUP by left(BldLabel,charindex('_',BldLabel)-1) ORDER BY BldLabel desc " ,objConnection,3,3    

however, not shure about what the GROUP BY is trying to do...
0
 
tmurray22Author Commented:
I get this error

Column 'cmpsub.bldlabel' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

the group by is beacause the data is formatted like
ABC_1.2_01
ABC_1.3_02
XYZ_1.2_01



0
 
tmurray22Author Commented:
I think the "top" only works on data for SQL 2005. But what do I know.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
rockymageeCommented:
You could just do 2 select max if the "top 2" is not working .... do the first select max and save out the unique key for that record then do a subsequent select max and in the where clause put not equal to the first record you pulled .... ?
0
 
tmurray22Author Commented:
I suppose I could use two selects, how would save the first value? use a querystring?    

0
 
tmurray22Author Commented:
Okay, I understand what is happening. My original sql returns a bunch of records that are then grouped by.
By asking to return only 2 the group by function becomes invalid. I will close this question and open a new one.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I understand now, you want the 2 highers bldlabel per group of the first part (before the _):

I assume that the field BldLabel comes from the table cmpsub:

objRS.Open "Select s.BldLabel from cmpsub s join cmpmap m on m.multisub = s.cmpname and m.subcode = s.codeline where m.codeline =  '" & strOS & "' and s.bldlabel in ( select top 2 i. from cmpsub i where left(i.BldLabel,charindex('_',i.BldLabel)-1)  = left(s.BldLabel,charindex('_',s.BldLabel)-1) order by ORDER BY i.BldLabel desc   ) ORDER BY s.BldLabel desc " ,objConnection,3,3    
0
 
tmurray22Author Commented:
thats what im trying do. get an error though

Incorrect syntax near the keyword 'from'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.