Solved

Sql aggregate not working

Posted on 2011-02-15
6
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 56

Expert Comment

by:HainKurt
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 56

Accepted Solution

by:
HainKurt 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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