We help IT Professionals succeed at work.

ASP SQL statement

Medium Priority
278 Views
Last Modified: 2010-04-06
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    
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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



Author

Commented:
I think the "top" only works on data for SQL 2005. But what do I know.
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 .... ?

Author

Commented:
I suppose I could use two selects, how would save the first value? use a querystring?    

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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    

Author

Commented:
thats what im trying do. get an error though

Incorrect syntax near the keyword 'from'
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.