[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Sql aggregate not working

I would like to choose the nc_assign_num from the query below, but I still get 3 records with an emplid that has 3 unique nc_assign_num.  What am I doing wrong?

Thanks,
Nigluc

SELECT EMPLID,MAX(NC_ASSIGN_NUM),
A.LAST_NAME_SRCH,
A.FIRST_NAME_SRCH,
(CONVERT(CHAR(10),A.HIRE_DT,121)),
(CONVERT(CHAR(10),A.START_DT,121)),
A.COUNTER,
A.TERM_REQUIRED,
A.NC_RATIONAL,
A.DESCR100,
A.JOBCODE,
A.JOBCODE_FILTR,
A.STEP,
A.RATE,
A.EMPLID

  FROM PS_NCHR8012_DATA A
  GROUP BY EMPLID,
(CONVERT(CHAR(10),A.HIRE_DT,121)),
(CONVERT(CHAR(10),A.START_DT,121)),
A.COUNTER,
A.TERM_REQUIRED,
A.NC_RATIONAL,
A.DESCR100,
A.JOBCODE,
A.JOBCODE_FILTR,
A.STEP,
A.RATE ,
  A.NC_NAME,
 A.LAST_NAME_SRCH,
 A.FIRST_NAME_SRCH
0
Lucia
Asked:
Lucia
1 Solution
 
Anil GolamariCommented:
Are you using A as alias name for the table if so you need to mention something like

 FROM PS_NCHR8012_DATA AS A.
0
 
Anil GolamariCommented:
please ignore above comment.
0
 
HainKurtSr. System AnalystCommented:
post the data for those 3 records...
there should be a difference on the values that you may not see easily, maybe a space at the end or upper/lower case
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LowfatspreadCommented:

you get multiple rows because aren't just doing this
SELECT EMPLID,MAX(NC_ASSIGN_NUM)
 FROM PS_NCHR8012_DATA A
  GROUP BY EMPLID

but are group by all the other columns as well...


perhaps you want this?


SELECT EMPLID,NC_ASSIGN_NUM,
A.LAST_NAME_SRCH, 
A.FIRST_NAME_SRCH, 
(CONVERT(CHAR(10),A.HIRE_DT,121)), 
(CONVERT(CHAR(10),A.START_DT,121)), 
A.COUNTER, 
A.TERM_REQUIRED, 
A.NC_RATIONAL, 
A.DESCR100, 
A.JOBCODE, 
A.JOBCODE_FILTR, 
A.STEP, 
A.RATE, 
A.EMPLID

  FROM (select a.*
              ,row_number() over (partition by emplid order by  NC_ASSIGN_NUM desc) as rn
          from PS_NCHR8012_DATA A) as A
where a.rn=1

Open in new window

0
 
HainKurtSr. System AnalystCommented:
or try this (if your sql support this, you should use this)

seelct * from (
  select row number() over (partition by EMPLID order by NC_ASSIGN_NUM desc) rn, a.*
     from PS_NCHR8012_DATA a
) x where rn=1

0
 
LuciaAuthor Commented:
Thank you all for responding.  Sorry I have been away.

Nigluc
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now