Re: Need help w/ Select statement

Question

Post reply

Re: Need help w/ Select statement

AJ-92543

SSC Eights!

Points: 852
More actions
June 10, 2005 at 1:43 pm

#186257

Hi all,

I am trying to get the highest bag # from the work order numbers w/ matching six digits.
But I’m having trouble with syntax.

This select statement works
SELECT     work_ord_num, work_ord_line_num, MAX(bag_num) AS bag_num
FROM         dbo.tblBag_data_Deflash
GROUP BY work_ord_num, work_ord_line_num
This returns
work_ord_num work_ord_line_num    bag_num
123456-00                               001                              1
123456-01                               001                              2

I need it to return the highest bag # of the work_ord_num by the first six digits. I need it to return
the second row only(123456-01        001            2)

I tried the select statement but it doesn’t work
SELECT     work_ord_num, work_ord_line_num, MAX(bag_num) AS bag_num
FROM         dbo.tblBag_data_Deflash
GROUP BY LEFT(work_ord_num, 6), work_ord_line_num

EM error:
dbo.tblBag_data_Deflash. work_ord_num is invalid in the select list because it not contained in either an aggregate function or the GROUP BY clause

Can anyone see where my error is? Thanks!!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This probabely doesn't work... but it may give you an idea.

Select dtWorks.L, Max(dtWorks.R) as work_ord_num, work_ord_line_num, bag_num

FROM

(

SELECT LEFT(work_ord_num, 6) as L, Right(work_ord_num, 2) as R, work_ord_line_num, MAX(bag_num) AS bag_num

FROM dbo.tblBag_data_Deflash

GROUP BY work_ord_num, work_ord_line_num

) dtWorks

group by...

You'll probabely need another derived table to finish this.

AJ-92543 SSC Eights! Points: 852 More actions · Answer 2

Hi Remi,

Thanks for your quick response.

I got it partially working w/ this Select statement. It's able group by the six digits and return the highest bag #, but it also only return the six digits work_ord_num instead of nine digits.

work_ord_num work_ord_line_num bag_num

123456 001 2

I need it to return

work_ord_num work_ord_line_num bag_num

123456-01 001 2

this is the Select statement

SELECT TOP 100 PERCENT LEFT(work_ord_num, 6) AS work_ord_num, work_ord_line_num, MAX(bag_num) AS bag_num

FROM dbo.tblBag_data_Deflash

GROUP BY LEFT(work_ord_num, 6), work_ord_line_num

ORDER BY work_ord_num

Is there a way I modified this to return work_ord_num? I tried adding work_ord_num in the statement then it just return both records again.

Thanks!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's why I was using max(Right(id, 2)) in a new column

Then you can reconcatenate it further down the lane .

But that's just a hunch as I've never done anything like this.

AJ-92543 SSC Eights! Points: 852 More actions · Answer 4

Hi Remi,

I tried your Select statement and it's giving me

Invalid column name ‘work_ord_num’. Would you know where the error is occurring? Or some other way to write this?

Thanks!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I told you that I didn't test the query. It was just to give you an idea. I've never had to write anything like this and frankly, I'm not too sure on how to proceed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Could you post the ddl to create the tables and insert some data?

It'll be easier to give a better shot at it.

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

I wasn't sure how work_ord_line_num played into this scenario, so I added some extra data just in case it also needed to be the maximum. Try this:

CREATE TABLE #tblBag_data_Deflash( work_ord_num varchar(25),

work_ord_line_num varchar(5),

bag_num integer)