Help Find Max record using SQL
Help Find Max record using SQL
(OP)
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
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
Talk To Other Members
RE: Help Find Max record using SQL
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help Find Max record using SQL
CODE
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
RE: Help Find Max record using SQL
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help Find Max record using SQL
CODE
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
RE: Help Find Max record using SQL
RE: Help Find Max record using SQL
What are the index in both tables ?
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help Find Max record using SQL
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:
CODE
Hope this helps.
RE: Help Find Max record using SQL
RE: Help Find Max record using SQL
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help Find Max record using SQL