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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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



tmurray22Author Commented:
I think the "top" only works on data for SQL 2005. But what do I know.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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 .... ?
tmurray22Author Commented:
I suppose I could use two selects, how would save the first value? use a querystring?    

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.
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    
tmurray22Author Commented:
thats what im trying do. get an error though

Incorrect syntax near the keyword 'from'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.