Find max and min number in group

[alwazeer]

hi all
what i need is:
show the great value for each group

max2 in group3 must be 2 not 5
her is a pic and db

 

[plog]

max2 in group3 must be 2 not 5

How do you figure?

Look at just the table, filter to Rg1=3. The largest value in the Rg1 column is 3. Please explain how you expect 5 to be returned.

I suspect you want to define a new grouping which would require a whole new query.

 

[arnelgp]

here is what i have:

SELECT TestTbl.Rg2, Min(TestTbl.Ltnum) AS MinOfLtnum, Max(TestTbl.Ltnum) AS MaxOfLtnum1, (SELECT TOP 1 T1.RG1 FROM TestTbl AS T1 WHERE T1.Rg2=TestTbl.Rg2 ORDER BY T1.ID DESC, T1.LTNUM DESC) AS Max2
FROM TestTbl
GROUP BY TestTbl.Rg2;

 

[arnelgp]

here's the final:

SELECT TestTbl.Rg2, Min(TestTbl.Ltnum) AS MinOfLtnum, Max(TestTbl.Ltnum) AS MaxOfLtnum1, (SELECT TOP 1 T1.RG1 FROM TestTbl AS T1 WHERE T1.Rg2=TestTbl.Rg2 ORDER BY T1.LTNUM & T1.RG1 DESC) AS Max2
FROM TestTbl
GROUP BY TestTbl.Rg2;

 

[alwazeer]

Thank you arnelgp so much, your Contribution were of a great help to me.

 

[arnelgp]

Youre welcone sir. I forgot to add ti the query the 2min. You only follow what i dit and instead if sortin it Desc sort it Asc, ascending