Query 400 record extraction with Maximum Value

[sureshpalani]

Dear All,

I need to write a query (QRY/400) and pick the records which has a maximum value for a certain numeric field.
lets say, for example

Name Marks
Tom 12
James 80
Tom 70
James 27
James 77
Tom 34
James 53

i need to pick only below records

Name Marks
Tom 70
James 80

Can somebody let me know if it is possible to do in QRY/400?

Regds,
Suresh

[Surya]

Re: Query 400 record extraction with Maximum Value

select name, Max(marks) from test
group by name 

[sureshpalani]

Re: Query 400 record extraction with Maximum Value

Hi Surya,

I am aware to use max() in SQL.
I would like to do in Query/400 as I am modifying an existing LIVE query.

Can you help me in this?

Regds,
Suresh

[GLS400]

Re: Query 400 record extraction with Maximum Value

Hi sureshpalani:

You will need to do 4 things in your qry/400

1. under Select sort fields: select names---10 marks---20
2. under Select report summary functions: select 4---maximum next to marks
3. under Define report breaks: select a level break on name (10). Default the remainder of the screens
4. under Select Output Type and Output Form: select 2-summary and whatever type of output you need


Best of Luck
GLS

[sureshpalani]

Re: Query 400 record extraction with Maximum Value

Hi GLS,

Thanks for the advice.
It works !!

In case, if i want to include few other fields and if i choose summary, the fields are not included in the report.
Is there a way to include the fields and get the records only with the maximum value as i mentioned earlier?

Thanks in Advance !

Regds,
Suresh

[GLS400]

Re: Query 400 record extraction with Maximum Value

Hi sureshpalani:

I'm just winging it here but off the top of my head try sorting on the other fields (all that you want printed).

Under Define report breaks Set a break on each of those other fields.

A screen will be presented for each break you set asking if you want to print/suppress sub-totals.

Suppress sub-totals for each break EXCEPT the last one.

Best of Luck
GLS

[FaStOnE]

Re: Query 400 record extraction with Maximum Value

Out of curiosity (which I know killed the cat) why can't you use SQL which would be a much easier way to do things?