[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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    
0
tmurray22
Asked:
tmurray22
  • 5
  • 2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now