unique value for multiple records 1

[evr72]

Hello,

I have two tables "rout" and "order"

My "rout" table contains ordernum, opnum, time
my "order" table contains ordernum, qty.

When I join both tables I would like to be able to get the "qty" for the fist "opnum" when I try to do this I get the "qty" for all the different "opnum"
I tried min(opseq) but when I group it it sums all the "qty" for each "opseq"

Not sure how to go about this one any help is appreciated

 

[PHV]

get the "qty" for the fist "opnum"
SELECT R.ordernum, R.Firstopnum, O.qty
FROM (SELECT ordernum, MIN(opnum) AS Firstopnum FROM rout GROUP BY ordernum
) R INNER JOIN order O ON R.ordernum = rdernum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

What do you mean by "the first" opnum?

I tried min(opseq)

Shouldn't this be min(opnum) ?


Randy

 

[evr72]

randy,

sorry made a mistake shoud be "opseq"

I don't think I explained very well

rout table
ordernum
opnum
clktime

order table
ordernum
qty


rout
ordernum opnum clktime
001 123 .75
002 123 .75
002 124 .99
002 125 .63
003 345 1.75
003 346 .5

ordernum qty
001 85
002 125
003 500

So what I am trying to do is a query from both tables that would give me this result

ordernum opnum clktime Qty
001 123 .75 85
002 123 .75 125
002 124 .99
002 125 .63
003 345 1.75 500
003 346 .5

so I was trying to do a

select ordernum, min(opnum)as opseq, clktime from rout this would be a nuew query call minop
then join minop and order table name it opqty then join opqty to my rout table

this would give me the first record for each table but in the minop query I would get the first opnum but the time for all operations would be summarized

 

[evr72]

I have to apologize PHV I did not follow your advice 'till today that worked awsome


Thank you!!!!