Max value of a specific field - Make query , How? 1

[cuok]

Hi friends!

I have a table with many records, ID is “Duplicates ok”
So I have many records for each ID.

What I’ tried to do -with no success- was to make a query that
Searches the max value of a specific field, for each ID, than Gives one record for each ID

TableORG

ID fld1 fld2 fldMAX fld4
1 3 2 2 7
1 4 2 1 3
2 3 7 6 7
1 3 2 5 4
3 7 3 8 7
1 3 2 9 6
2 6 2 5 7
3 3 2 5 7

I’m interesting only in fldMAX so what I want to get is a query gives”

ID fldMAX
1 9
2 6
3 8

I hope I was clear enough

Thanks in advance

CUOK

 

[swampBoogie]

select id,max(fldmax)
  from t
 group by id

 

[cuok]

WOW !!!

thank you very much swampBoogie !

i'm going to try it right now then i tell what i got!!
CUOK

 

[cuok]

HI swampBoogie !!
Its worked excellent and so you!!
Thank you very much!
CUOK

 

[cuok]

swampBoogie1

I HOPE YOU ARE STILL THERE!

CAN I make the same query, but now made of 3 different tables?

CUOK

 

[swampBoogie]

I'm here, but I'm not sure what you mean by 3 different tables.

Maybe

select id,max(fldmax) from (
select id,fldmax
  from t1
 union all
select id,fldmax
  from t2
union all
select id,fldmax
  from t3)
group by id

If this is completly wrong, explain more.

 

[cuok]

I THINK IT IS WHAT I NEED I'M TRYING IT NOW!
CUOK

 

[cuok]

hI swampBoogie

I WAS NOT SO CLEAR, SORRY I TRY AGAIN:
the first table was:

TableORG

ID fld1 fld2 fldMAX fld4
1 3 2 2 7
1 4 2 1 3
2 3 7 6 7
1 3 2 5 4
3 7 3 8 7
1 3 2 9 6
2 6 2 5 7
3 3 2 5 7


ID fldMAX
1 9
2 6
3 8

the seconed table is:

TableBIO

ID fldA fldB BIOMAX fld4
1 3 2 2 7
2 4 2 1 3
2 3 7 6 7
1 3 2 5 4
1 7 3 8 7
1 3 2 9 6
2 6 2 5 9
3 3 2 5 7


ID BIOMAX
1 7
2 9
3 7

NOW I HAVE TO GET:

ID fldMAX BIOMAX
1 9 7
2 6 9
3 8 7

THE 3TH TABLE WILL MAKE:
ID fldMAX BIOMAX NPDMAX
1 9 7 6
2 6 9 9
3 8 7 8

I HOPE I 'M MORE CLEARER NOW!

THANKS A LOT
CUOK

 

[swampBoogie]

select id,max(fldmax),
(select max(biomax) from tableBio
  where id = tableorg.id ),
(select max(npdmax) from tablenpd
  where id = tableorg.id )
 from tableorg

 

[cuok]

swampBoogie -

YOU REALLY ARE AN EXPERT !!

ITS WORKS FINE !!

cuok