Solved

Sql aggregate not working

Posted on 2011-02-15
6
275 Views
Last Modified: 2012-05-11
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
Comment
Question by:Lucia
6 Comments
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 34900195
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
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 34900215
please ignore above comment.
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34900232
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34901023

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
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 34901294
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
 

Author Closing Comment

by:Lucia
ID: 34919159
Thank you all for responding.  Sorry I have been away.

Nigluc
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question