Solved

Sql aggregate not working

Posted on 2011-02-15
6
262 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: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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

831 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