Query

[Harish]

Hi I have three columns
i want the result as shown below(sewcond set)
i want find out the column 2 where col3 is maximum but less than 140
group by col1

Data
col1.....col2....col3
a123.....141.....123
a123.....897.....124
a124.....187.....124
a125.....153.....121
a125.....173.....126
a126.....153.....120
a126.....121.....128
a126.....198.....134
a126.....159.....150
a127.....198.....134
a127.....324.....129
a127.....398.....135
a127.....230.....140

result set

col1.....col2....col3
a123.....897.....124
a124.....187.....124
a125.....173.....126
a126.....198.....134
a127.....398.....135

Thanks,
Harish

[Prabhu]

harish,

try this out...just replace the table names..

select * from forum a where col2 > col3 and col2 =
(Select max(col2) from forum b where b.col1=a.col1 ) and
col3 < 140

Regards
Prabhu


------------
Harish at 9/12/01 3:36:58 PM


Hi I have three columns
i want the result as shown below(sewcond set)
i want find out the column 2 where col3 is maximum but less than 140
group by col1

Data
col1.....col2....col3
a123.....141.....123
a123.....897.....124
a124.....187.....124
a125.....153.....121
a125.....173.....126
a126.....153.....120
a126.....121.....128
a126.....198.....134
a126.....159.....150
a127.....198.....134
a127.....324.....129
a127.....398.....135
a127.....230.....140

result set

col1.....col2....col3
a123.....897.....124
a124.....187.....124
a125.....173.....126
a126.....198.....134
a127.....398.....135

Thanks,
Harish