Select MAX value 1

[scottydd]

I was wondering if someone could help me with at query.

I have 2 fields dwg_rev and dwg_rev_seq.
dwg_rev is at single letter and dwg_rev_seq is a number or NULL.

Can someone tell me how i could go about selecting the max letter and the corresponding max number?

Thanks,
Scott

 

[gradley]

Select dwg_rev, dwg_rev_seq
from table1
where dwg_rev = (Select MAX(dwg_rev) from Table1)

 

[gradley]

If the above is not what you were looking for, here is both max values in a cross join

select max(a.dwg_rev), max(b.dwg_rev_seq)
from table1 a
cross join table1 b

Hopefully one of these examples is what you were looking for.

 

[scottydd]

gradley,

that is what i am look for, but i forgot a column. I also have a dwg_no column. i need the dwg_no and the corresponding max dwg_rev and dwg_rev_seq.

Sorry about that, been staring at the screen to long.

Thanks again,
Scott

 

[scottydd]

I think i got it:

select a.dwg_no, max(a.dwg_rev), max(b.dwg_rev_seq)
from dwgdetail a
cross join dwgdetail b
group by a.dwg_no


Thank again,
Scott

 

[gradley]

You can probably get away without the cross-join for the data you are looking for:

select dwg_no, max(dwg_rev), max(dwg_rev_seq)
from dwgdetail 
group by dwg_no

Will give you a max for both within dwg_no. Even though I originally mentioned cross-join earlier, it is not necessary here.

 

[scottydd]

Thanks, that worked.

 

[SQLSister]

Be aware that the two max values will not necessarily be from the same reocrd.

Questions about posting. See faq183-874