Select Max Value + Group By

# Thread: Select Max Value + Group By

1. Das
Member
Join Date
Dec 1969
Posts
57

## Select Max Value + Group By

Hello all,<BR> In my Procedure I want to Get an ID from a Select statement to use it for other DDL operations. For Each ID there is a PID, which are versions of the ID: 1-&#062;Many (ID-PID)<BR><BR>Example: <BR>ID Â* Â* PID<BR>10 Â* Â* 10.1<BR>10 Â* Â* 10.7<BR>10 Â* Â* 10.3<BR>11 Â* Â* 11.1<BR><BR>From this I want to Get the PID&#039;s with the latest version(the number after the decimal determines the latest versoin).Here is what I am doing<BR><BR>SELECT MAX(TO_NUMBER(SUBSTR(PID, INSTR(PID, &#039;.&#039;)+1)))<BR>FROM MHO<BR>GROUP BY ID<BR><BR>With this I get the Max PID&#039;s Decimal Numbers but not the Exact PID.<BR><BR>Any Help Appreciated.<BR>thanks<BR>Das

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Assuming that PID is a number...

...and *NOT* a string, then it&#039;s easy:<BR><BR>SELECT INT(PID), MAX(PID) As MaxPid<BR>FROM MHO<BR>GROUP BY INT(PID)<BR><BR>Or, if you really have *both* the ID and PID fields you show (not needed, if you use the INT trick, but...), then:<BR><BR>SELECT ID, MAX(PID) as MaxPID<BR>FROM MHO<BR>GROUP BY ID<BR><BR>If you really do want *only* the part after the decimal point [any reason?] then<BR><BR>SELECT ID, MAX( PID-INT(PID) ) As MaxPID<BR>FROM MHO<BR>GROUP BY ID<BR><BR>HOWEVER...<BR><BR>If you foolishly used things like <BR> 10.1<BR> 10.7<BR> 10.13<BR> 10.21<BR>where you want "21" as the answer (tch! should have padded the decimal parts when you added them on! 10.001, 10.007, etc.), then, yeah, you&#039;ll have to do something ugly like you are doing.<BR><BR>I don&#039;t see any way to return the complete PID "along side" the part to right of decimal. You&#039;ll just have to recombine with the ID, I guess.<BR><BR>

3. Das
Member
Join Date
Dec 1969
Posts
57

## RE: Assuming that PID is a number...

No PID/ID is not a Number, What I showed were examples, Since its always has a number after the decimal I fugured it would be easy to determine the max PID from among them.<BR><BR>All I want to get is the PID (Whole) the decimal numbers I am displaying are just for determining the Max among them. <BR>Any Idea on how to just display the Max PID + Group by ID<BR><BR>thanks<BR>das

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ahhh! I see...

Ugh... Probably have to join the table back to itself? Unless the stuff to left of the period is the same for every PID for the same ID???<BR><BR>ID -- PID<BR>10 -- XYZ.182<BR>10 -- XYZ.191<BR>10 -- XYZ.23<BR><BR>That we could do. But if it&#039;s<BR><BR>ID -- PID<BR>10 -- XYZ.182<BR>10 -- ABC.191<BR>10 -- POT.23<BR><BR>Then I don&#039;t see how to do it without joining back to the original table.<BR><BR>You&#039;d do <BR> SELECT ID, PID <BR> FROM table AS T1,<BR> ( SELECT ID, MAX(stuffafterperiod) AS MX FROM table ) AS T2<BR> WHERE T1.ID = T2.ID<BR> AND T1.PID LIKE &#039;%.&#039;+CONVERT(VarChar, T2.MX)<BR><BR>???<BR><BR>

5. Das
Member
Join Date
Dec 1969
Posts
57

## RE: Ahhh! I see...

the stuff to left of the period is the same for every PID for the same ID.<BR><BR>Hope this will get me the right answer now.<BR><BR>thanks much<BR>das<BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay! Then better:

SELECT ID, SUBSTRING(PID,1,CHARINDEX(PID,&#039;.&#039;)) AS LeftPID, MAX(...funnystuff...) AS RightPID<BR>FROM table <BR>GROUP BY ID, SUBSTRING(PID,1,CHARINDEX(PID,&#039;.&#039;))<BR>< BR>Yes??? Won&#039;t that work?<BR><BR>Then you just have to re-combine the LeftPID and RightPID in any query that makes use of this as a subquery (or joins to it).<BR><BR>[I may have CHARINDEX backwards...think I do, in fact. But you get the idea.]<BR><BR>

7. Das
Member
Join Date
Dec 1969
Posts
57

## RE: Okay! Then better:

Got it, Actually I tried the Join to itself too but it made the query a complex one. The one above is nice.<BR><BR>thanks for the help. B.T.W I am using oracle<BR><BR>thanks a lot<BR><BR>peace<BR>das

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Surprising Oracle couldn't do that...

...even little old Access managed to handle it.<BR><BR>SELECT *<BR>FROM pid AS P1, [SELECT id, MAX(CINT(MID([pidv],INSTR([pidv],&#039;.&#039;)+1))) AS mx<BR> FROM pid<BR> GROUP BY id] AS P2<BR>WHERE P1.id=P2.id And P1.pidv Like &#039;%.&#039;+CSTR(p2.mx)<BR>ORDER BY P1.id<BR><BR>Got the right results. (Access had a weird glitch; it didn&#039;t like the field name pid being same as table name pid in the inner select. Changed the field name and it worked. Go figure.)<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•