Catch only last query sql server query

Navigation
1

I have the following query: 

SELECT  
     CODIGO,
     DTINICIO AS INICIO,
     DTFINAL  AS  FINAL
    FROM PFHSTAFT
    WHERE
     EMPRESA =1 
        GROUP BY
          CODIGO,
          DTFINAL,
          DTINICIO

It returns as follows: 

0031    2000-11-06 00:00:00.000 2001-12-31 00:00:00.000
0031    2002-02-26 00:00:00.000 2002-06-30 00:00:00.000
0031    2002-09-26 00:00:00.000 2008-03-17 00:00:00.000  <<<<<
0051    2003-04-23 00:00:00.000 2016-09-05 00:00:00.000
0051    2016-10-06 00:00:00.000 2016-10-15 00:00:00.000
0051    2016-10-18 00:00:00.000 2016-10-22 00:00:00.000  <<<<<
0051    2016-11-01 00:00:00.000 NULL

But I need to list only the last end date of each employee, which is represented by the code: 

 0031   2002-09-26 00:00:00.000 2008-03-17 00:00:00.000
 0051   2016-10-18 00:00:00.000 2016-10-22 00:00:00.000

I tried to use max but it did not answer; does anyone know what it can be?

BANK: SQL SERVER 2008

    
asked by anonymous 08.07.2017 / 00:03

2 answers

4

There are several alternatives, I'll leave here one that makes use of the "window function" ROW_NUMBER . 

;WITH cte AS 
(
    SELECT  EMPRESA,
            CODIGO,
            DTINICIO AS INICIO,
            DTFINAL  AS  FINAL,
            ROW_NUMBER() OVER (PARTITION BY EMPRESA, CODIGO ORDER BY DTFINAL DESC) RN
      FROM PFHSTAFT  
)
SELECT CODIGO, 
       INICIO,
       FINAL
  FROM cte
 WHERE RN = 1
   AND EMPRESA = 1
    
08.07.2017 / 12:23
0

You can test: 

SELECT * FROM TABELA A WHERE A.DATA = (SELECT MAX(A1.DATA) FROM TABELA A1 WHERE A1. CHAVE = A.CHAVE... AND A1.DATA <= SYSDATE);

Note: If Oracle is SYSDATE . 

SELECT A.CODIGO, A.DTINICIO AS INICIO, A.DTFINAL AS FINAL FROM PFHSTAFT A WHERE A.DTFINAL = (SELECT MAX(A1.DTFINAL) FROM PFHSTAFT A1 WHERE A1.DTFINAL <= SYSDATE) AND EMPRESA =1 GROUP BY A.CODIGO, A.DTFINAL, A.DTINICIO;

Since DTFINAL is a key to your table, you say that you want to the DTFINAL field the largest date (for the same keys). So use MAX and make a join with the subselect table. SYSDATE you say that the highest date has to be less than or equal to the current date.

    
10.07.2017 / 23:29
What is the unless in Ruby command? Failure / Doubt change jquery event