Help Find Max record using SQL

[ejaggers]

I only want the record with the largest FTE, but I don’t want the FTE in the output.

example:
Select a, b, c from table where x = MAX(x)

Data:
a b c x
---------------------
sam 111 aaa 1
sam 222 bbb 2
sam 333 ccc 3
bob 555 eee 8
bob 222 bbb 4
bob 999 zzz 6

Output:
sam 333 ccc
bob 555 eee


The following seems to work, at least I think it does, so how can I get the result w/o MAX(prep_fte_pct) in the output?

SELECT M.prem_emp AS EMPLOYEEID,
M.prem_p_jclass AS JOBCLASS,
M.prem_p_bargain AS BARGAINING,
M.prem_loc AS DEPARTMENT,
MAX(prep_fte_pct)
FROM prempmst M, premppay P
WHERE M.prem_proj = P.prep_proj
AND M.prem_emp = P.prep_emp
AND M.prem_proj = 0
AND M.prem_emp = 91
AND P.prep_pay BETWEEN 100 AND 199
GROUP BY M.prem_emp,M.prem_p_jclass,M.prem_p_bargain,M.prem_loc

 

[PHV]

Please, post the schema of the 2 tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ejaggers]

I said that the above works (I thought), but it does not.

Table Name: prempmst
  
  Column name          Type                                    Nulls
  
  prem_proj            smallint                                yes
  prem_emp             integer                                 yes
  prem_lname           char(20)                                yes
  prem_fname           char(15)                                yes
  prem_minit           char(1)                                 yes
  prem_suffix          char(3)                                 yes
  prem_prev_lname      char(20)                                yes
  prem_prev_fname      char(15)                                yes
  prem_prev_minit      char(1)                                 yes
  prem_eeo_function    char(2)                                 yes
  prem_ssn             char(11)                                yes
  prem_home_ph         char(14)                                yes
  prem_marital         char(1)                                 yes
  prem_gender          char(1)                                 yes
  prem_eeo_race        char(2)                                 yes
  prem_eeo_ft          char(1)                                 yes
  prem_blood           char(3)                                 yes
  prem_act_stat        char(1)                                 yes
  prem_inact           char(4)                                 yes
  prem_inact_date      date                                    yes
  prem_term            char(4)                                 yes
  prem_term_date       date                                    yes
  prem_aft_date        date                                    yes
  prem_aft_run         char(1)                                 yes
  prem_pr_typv         char(1)                                 yes
  prem_p_freq          char(1)                                 yes
  prem_unem_tax        char(1)                                 yes
  prem_retire          char(1)                                 yes
  prem_p_bargain       char(4)                                 yes
  prem_p_org           char(8)                                 yes
  prem_d_proj          char(5)                                 yes
  prem_entity          char(1)                                 yes
  prem_loc             char(4)                                 yes
  prem_p_jclass        char(4)                                 yes
  prem_status          char(2)                                 yes
  prem_chg_id          char(20)                                yes
  prem_chg_date        date                                    yes
  prem_chg_time        char(8)                                 yes
  prem_dob             date                                    yes
  prem_hire            date                                    yes
  prem_perm            date                                    yes
  prem_service         date                                    yes
  prem_add_yn          char(1)                                 yes
  prem_phone_yn        char(1)                                 yes
  prem_dep_yn          char(1)                                 yes
  prem_emer_yn         char(1)                                 yes
  prem_spouse          integer                                 yes
  prem_bank            char(3)                                 yes
  prem_bank_acct       char(25)                                yes
  prem_memo            char(40)                                yes
  prem_pr_typ0         char(1)                                 yes
  prem_pr_typ1         char(1)                                 yes
  prem_pr_typ2         char(1)                                 yes
  prem_pr_typ3         char(1)                                 yes
  prem_pr_typ4         char(1)                                 yes
  prem_pr_typ5         char(1)                                 yes
  prem_pr_typ6         char(1)                                 yes
  prem_pr_typ7         char(1)                                 yes
  prem_pr_typ8         char(1)                                 yes
  prem_pr_typ9         char(1)                                 yes
  prem_wkloc           char(4)                                 yes
  prem_p_obj           char(6)                                 yes
  prem_pension_date    date                                    yes
  prem_orig            date                                    yes
  prem_resident        char(1)                                 yes
  prem_cafeteria       char(1)                                 yes
  prem_sick_bank       char(1)                                 yes
  prem_city_code       char(4)                                 yes
  prem_mil_serv        smallint                                yes
  prem_ext_serv        smallint                                yes
  prem_oth_serv        smallint                                yes
  prem_pj_prevent      char(1)                                 yes
  prem_filler          char(50)                                yes
  prem_supervisor      integer                                 yes
  prem_email           char(50)                                yes
  prem_adv_del         char(1)                                 yes
  prem_confid          char(4)                                 yes
  prem_mname           char(15)                                yes
  prem_alt_email       char(50)                                yes
  prem_userdef1        char(20)                                yes
  prem_userdef2        char(20)                                yes
  prem_userdef3        integer                                 yes
  prem_userdef4        integer                                 yes
  prem_userdef5        date                                    yes
  prem_userdef6        decimal(8,4)                            yes
  prem_new_hire        char(1)                                 yes
  prem_high_degree     char(4)                                 yes
  prem_dep_cd          char(5)                                 yes

Table Name: prjobcls
  
  Column name          Type                                    Nulls
  
  prjb_proj            smallint                                yes
  prjb_code            char(4)                                 no
  prjb_short           char(10)                                yes
  prjb_long            char(30)                                yes
  prjb_cat1            char(4)                                 yes
  prjb_cat2            char(4)                                 yes
  prjb_status          char(2)                                 yes
  prjb_barg_unit       char(4)                                 yes
  prjb_loc             char(4)                                 yes
  prjb_non_pyrl        char(1)                                 yes
  prjb_eeo_ft          char(1)                                 yes
  prjb_eeo_class       char(4)                                 yes
  prjb_eeo_function    char(4)                                 yes
  prjb_risk            char(8)                                 yes
  prjb_basepay         smallint                                yes
  prjb_flsa_exmt       smallint                                yes
  prjb_pos_ctrl        char(1)                                 yes
  prjb_grstep          char(1)                                 yes
  prjb_min_grade       char(4)                                 yes
  prjb_max_grade       char(4)                                 yes
  prjb_min_step        smallint                                yes
  prjb_max_step        smallint                                yes
  prjb_pay_scale       char(1)                                 yes
  prjb_min_pay         decimal(11,4)                           yes
  prjb_max_pay         decimal(11,4)                           yes
  prjb_freq            char(1)                                 yes
  prjb_annper          decimal(5,3)                            yes
  prjb_sched_hrs       decimal(6,2)                            yes
  prjb_org             char(8)                                 yes
  prjb_obj             char(6)                                 yes
  prjb_proja           char(5)                                 yes
  prjb_teacher         char(1)                                 yes
  prjb_sub             char(1)                                 yes
  prjb_calc_code       char(2)                                 yes
  prjb_hrs_per_day     decimal(6,2)                            yes
  prjb_hrs_per_yr      decimal(6,2)                            yes
  prjb_days_per_yr     decimal(5,2)                            yes
  prjb_emp_type        char(4)                                 yes
  prjb_sub_code        smallint                                yes
  prjb_long_type       smallint                                yes
  prjb_day_month       decimal(4,2)                            yes
  prjb_incr_days       char(1)                                 yes
  prjb_filler          char(50)                                yes
  prjb_reference       char(8)                                 yes
  prjb_gen_labor       char(1)                                 yes
  prjb_hold_org        char(8)                                 yes
  prjb_hold_obj        char(6)                                 yes
  prjb_hold_d_proj     char(5)                                 yes
  prjb_mid_pay         decimal(11,4)                           yes
  prjb_mark_pay        decimal(11,4)                           yes
  prjb_userdef1        char(20)                                yes
  prjb_userdef2        char(20)                                yes
  prjb_state_pos       char(4)                                 yes
  prjb_role_id         smallint                                yes
  prjb_service_id      char(8)                                 yes
  prjb_daysw_fact      char(1)                                 yes
  prjb_seg1            char(4)                                 yes
  prjb_seg2            char(10)                                yes
  prjb_seg3            char(10)                                yes
  prjb_seg4            char(10)                                yes
  prjb_seg5            char(10)                                yes
  prjb_seg6            char(10)                                yes
  prjb_seg7            char(10)                                yes
  prjb_seg8            char(10)                                yes

 

[PHV]

Well, prjobcls ...
What are the 2 tables you want to query and their relationships ?
Which columns should be displayed and what are the criterias ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ejaggers]

Sorry, wrong table (prjobcls). It's the pay table. I only want the records for the largest FTE(s) (prep_fte_pct) for each employee.

Table Name: premppay
  
  Column name          Type                                    Nulls
  
  prep_proj            smallint                                yes
  prep_emp             integer                                 yes
  prep_job             char(4)                                 yes
  prep_rec_scr         char(1)                                 yes
  prep_pay             smallint                                yes
  prep_seq             smallint                                yes
  prep_loc             char(4)                                 yes
  prep_bgnu            char(4)                                 yes
  prep_calc_code       char(2)                                 yes
  prep_pos             integer                                 yes
  prep_grade           char(4)                                 yes
  prep_step            smallint                                yes
  prep_step_date       date                                    yes
  prep_step_date2      date                                    yes
  prep_mult_fact       decimal(5,4)                            yes
  prep_days_per_yr     decimal(6,2)                            yes
  prep_perhrs          decimal(6,2)                            yes
  prep_dayhrs          decimal(6,2)                            yes
  prep_pay_periods     decimal(6,4)                            yes
  prep_freq            char(1)                                 yes
  prep_pay_scale       char(1)                                 yes
  prep_wk_sun          char(1)                                 yes
  prep_wk_mon          char(1)                                 yes
  prep_wk_tues         char(1)                                 yes
  prep_wk_wed          char(1)                                 yes
  prep_wk_thur         char(1)                                 yes
  prep_wk_fri          char(1)                                 yes
  prep_wk_sat          char(1)                                 yes
  prep_cyc1            char(1)                                 yes
  prep_cyc2            char(1)                                 yes
  prep_cyc3            char(1)                                 yes
  prep_cyc4            char(1)                                 yes
  prep_cyc5            char(1)                                 yes
  prep_fte_pct         decimal(5,4)                            yes
  prep_ann_sal         decimal(9,2)                            yes
  prep_per_sal         decimal(9,2)                            yes
  prep_daily_rt        decimal(8,4)                            yes
  prep_hrly_rt         decimal(8,4)                            yes
  prep_limit           decimal(9,2)                            yes
  prep_risk            char(8)                                 yes
  prep_org             char(8)                                 yes
  prep_obj             char(6)                                 yes
  prep_d_proj          char(5)                                 yes
  prep_start           date                                    yes
  prep_end             date                                    yes
  prep_alloc           integer                                 yes
  prep_ref_sal         decimal(9,2)                            yes
  prep_ded_flag        char(1)                                 yes
  prep_add_to_base     char(1)                                 yes
  prep_base_pay        char(1)                                 yes
  prep_ann_sal1        decimal(9,2)                            yes
  prep_per_sal1        decimal(9,2)                            yes
  prep_daily_rt1       decimal(8,4)                            yes
  prep_hrly_rt1        decimal(8,4)                            yes
  prep_limit1          decimal(9,2)                            yes
  prep_last_review     date                                    yes
  prep_next_review     date                                    yes
  prep_status          char(2)                                 yes
  prep_grade2          char(4)                                 yes
  prep_step2           smallint                                yes
  prep_pay_periods2    decimal(6,4)                            yes
  prep_days_per_yr2    decimal(6,2)                            yes
  prep_perhrs2         decimal(6,2)                            yes
  prep_ann_sal2        decimal(9,2)                            yes
  prep_per_sal2        decimal(9,2)                            yes
  prep_daily_rt2       decimal(8,4)                            yes
  prep_hrly_rt2        decimal(8,4)                            yes
  prep_limit2          decimal(9,2)                            yes
  prep_contract        char(4)                                 yes
  prep_emp_type        char(4)                                 yes
  prep_grade_level     char(2)                                 yes
  prep_pay_scr         char(1)                                 yes
  prep_pay_months      char(2)                                 yes
  prep_tenure_date     date                                    yes
  prep_contrct_date    date                                    yes
  prep_hire_app_date   date                                    yes
  prep_hire_time       char(8)                                 yes
  prep_benefit_stat    char(2)                                 yes
  prep_balloon         char(1)                                 yes
  prep_civil_serv      char(8)                                 yes
  prep_prior_mths      decimal(4,1)                            yes
  prep_prior_yrs       decimal(3,1)                            yes
  prep_years_here      decimal(3,1)                            yes
  prep_hrs_per_yr      decimal(6,2)                            yes
  prep_days_period     decimal(5,2)                            yes
  prep_proj_sal        decimal(9,2)                            yes
  prep_remain_sal      decimal(9,2)                            yes
  prep_calndr          smallint                                yes
  prep_wrk_sched       smallint                                yes
  prep_frozen          char(1)                                 yes
  prep_encumber        char(1)                                 yes
  prep_remain_pays     decimal(6,4)                            yes
  prep_last_hrly_rt    decimal(8,4)                            yes
  prep_last_daily_rt   decimal(7,4)                            yes
  prep_last_per_sal    decimal(9,2)                            yes
  prep_last_step_dt    date                                    yes
  prep_filler          char(50)                                yes
  prep_subj            char(3)                                 yes
  prep_pend_pos        integer                                 yes
  prep_civ_class       char(4)                                 yes
  prep_civ_cl_stat     char(4)                                 yes
  prep_civ_s_date      date                                    yes
  prep_civ_prb_date    date                                    yes
  prep_civ_ex_date     date                                    yes
  prep_civ_ret_date    date                                    yes
  prep_civ_desig       char(4)                                 yes
  prep_civ_de_stat     char(4)                                 yes
  prep_civ_comment     char(50)                                yes
  prep_civ_ret_num     char(9)                                 yes
  prep_userdef1        char(20)                                yes
  prep_userdef2        char(20)                                yes
  prep_userdef3        integer                                 yes
  prep_userdef4        date                                    yes
  prep_userdef5        decimal(10,4)                           yes
  prep_inactive        char(1)                                 yes
  prep_flsa_ot         smallint                                yes

 

[ejaggers]

PHV, Thanks for looking at this, and I'm sure your time is very valuable. My requirements have changed and I no longer have a need for this. HOWEVER, using the sample table above, I still would like to know if, and how it could be done.

 

[PHV]

What are the relationships between premppay and prempmst ?
What are the index in both tables ?


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mberni]

Hi,
i don't know which version of Informix you are using, but in newer versions (we use 11.50) you can do something like that:

select first 1 column1 from (select column1, max(column2) from table1 group by 1 order by 2 desc);

Hope this helps.

 

[ejaggers]

mberni, please explain this stmt. Thanks....

 

[PHV]

ejaggers, as you didn't reply to my thread timestamped 5 Dec 09 8:50, I give you a suggestion based on your first post:
SELECT A.a, A.b, A.c FROM table A
WHERE A.x=(SELECT MAX(x) FROM table WHERE a=A.a)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ejaggers]

Thanks for the above example.