topic Re: Selecting min value in Application Development and Automation Discussions

Selecting min value

Former Member — Mon, 02 Oct 2006 14:45:01 GMT

I coded a select stmt which should return a min value and it is not returning correctly.

select f1 MIN( distinct f2) as f2

f3 f4

into corresponding fields of table itab

from db1

where f1 in r_f1

and f3 = <constant>

group by f1 f3 f4.

There are 3 rows with f2 = 1, 2 ,3 in the databse table for same f1. I am expecting it would return <b>only</b> the row with f2 = 1. But this select stmt is getting all the 3 rows into the internal table. Can somebody help me figure out what I did wrong?

Thanks

Anu

Re: Selecting min value

Former Member — Mon, 02 Oct 2006 14:53:27 GMT

Hi Anupama,

select f1 <b>DISTINCT( MIN( f2) )</b> as f2

f3 f4

into corresponding fields of table itab

from db1

where f1 in r_f1

and f3 = <constant>

group by f1 f3 f4.

Thanks,

Vinay

Re: Selecting min value

Former Member — Mon, 02 Oct 2006 14:58:52 GMT

Hi Anu

Your code will return one row for each line that satisfies your Where clause and is unique to all other lines in the set. since f2 contains values 1,2 and 3 you will get 1 row for each (so long as there is a row with these values that has the correct values of f1 and f3.

I think it would be far simpler to do the following:


SELECT f1 f2 f3 f4
INTO CORRESPONDING FIELDS OF TABLE itab
FROM db1
WHERE f1 IN r_f1
AND f3 = <constant>.

SORT itab BY f1 f3 f4 f2 DESCENDING 
DELETE ADJACENT DUPLICATES FROM itab COMPARING f1 f3 f4.

I've not tested this but it should leave you with one line containing the minimum of f2 and grouped by f1 f3 and f4. It will also be a more efficient Select as the internal optimiser will be able to work (aggregate functions and Group by etc prevent this.

I hope this helps.

Andy

Sorry, just changed the sort order!

Message was edited by: Andrew Wright

Re: Selecting min value

sridhar_k1 — Mon, 02 Oct 2006 15:08:04 GMT

You are getting 3 rows because, there may be 3 different values for F4 in the table. Remove the field from selection or use delete adjacent as mentioned above.

Regards

Sridhar

Re: Selecting min value

Former Member — Mon, 02 Oct 2006 15:13:11 GMT

Hi,

group by only f1 to get correct output

select f1 MIN( distinct f2) as f2

into corresponding fields of table itab

from db1

where f1 in r_f1

and f3 = <constant>

group by f1 .

Regards

amole

Re: Selecting min value

Former Member — Mon, 02 Oct 2006 15:14:31 GMT

Thank you Andrew.

Actually I coded this way at first, but then thought I am re-inventing the wheel, why not let select stmt do it for me and then changed the code. But now I realise why it will not work.

Thanks,

Anu