0

I have this inner join from staging and operations table jobsCode I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 recordsenter image description here
original query

SELECT a.JobCode 
    ,b.ID
    ,b.Start_Date   
    ,a.EndDate  
    ,b.Start_Date  
    ,b.End_Date
FROM [RPStage].[dbo].[GLBJobCode] a 
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date;

trying to get it using max function

SELECT a.JobCode 
    ,MAX(b.ID) ID
    ,MAX(B.Start_Date) Start_Date  
    ,a.EndDate  
    ,b.Start_Date  
    ,b.End_Date
FROM [Stage].[dbo].[JobCode] a 
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date
GROUP BY a.JobCode
    ,b.ID
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date;
3
  • Do you want the MAX(b.ID) or the MAX(b.Start_Date)? These could belong to different rows. – ypercubeᵀᴹ Oct 8 '14 at 12:57
  • I am trying to get both to do update opreation – NinjaDeveloper Oct 8 '14 at 13:04
  • You need to find IDs first and query for the date. Using MAX won't work here since it returns every non-unique combination for non grouping columns. – yahor Oct 8 '14 at 19:41
1

Window Functions, or the OVER() Clause

It appears that what you are looking for is a window function or an OVER() clause.

In your original example, you are trying to use two max() conditions, which doesn't work, because when you try to then apply GROUP BY, you can have a condition where the max of the first column ID and the second column startdate aren't in the same row, and so then GROUP BY simply can't be understood.

However, if you are looking to extract the max over a grouping, and you can define that grouping, and you want to obtain the 'column-wise' maximum for more than one column for that grouping (as you seem to want to do), then here's the solution.

CREATE TABLE test (
  jobcode integer,
  id integer, 
  a_startdate datetime,
  a_enddate datetime,
  b_startdate datetime,
  b_enddate datetime);

  INSERT INTO test VALUES (513801, 7136, '11-01-2011', '12-31-9998', '11-01-2011', '12-31-9998');
  INSERT INTO test VALUES (513801, 7137,'04-26-2014', '12-31-9998','04-26-2014', '12-31-9998');

I first made this table to recreate your input data in the picture. I then applied this query.

SELECT jobcode, max(id) OVER (PARTITION BY jobcode), 
max(a_startdate) OVER(PARTITION BY jobcode), 
a_enddate,  b_startdate, b_enddate FROM test;

Try out this SQL Fiddle, and see if it gives the results you're looking for. I did the best I could with the description I had.

0

You are grouping by b.Start_Date and for each row aggregate MAX(B.Start_Date) always the same as b.Start_Date. Just replace b.Start_Date with a.Start_Date

SELECT a.JobCode 
    ,MAX(b.ID) ID
    ,MAX(B.Start_Date) Start_Date  
    ,a.EndDate  
    ,a.Start_Date  
FROM [Stage].[dbo].[JobCode] a 
INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
    AND a.EndDate = b.End_Date
    AND a.EndDate = '9998-12-31'
    AND a.StartDate != b.Start_Date
GROUP BY a.JobCode
    ,b.ID
    ,a.EndDate
    ,a.Start_Date;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.