Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Find Max record using SQL

Status
Not open for further replies.

ejaggers

Programmer
Feb 26, 2005
148
US
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
 
Please, post the schema of the 2 tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I said that the above works (I thought), but it does not.

Code:
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
 
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
 
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.

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
 
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.
 
What are the relationships between premppay and prempmst ?
What are the index in both tables ?


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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:

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

Hope this helps.
 
mberni, please explain this stmt. Thanks....
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top