Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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