Get only last query sql server record

Question

Get only last query sql server record

Asked 7 years, 4 months ago

Viewed 3,449 times

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 final 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 did not answer me; someone knows what can be?

DATABASE: SQL SERVER 2008

Rows where the date columns are without information (NULL) should be ignored?

– José Diz

2017/07/08 at 11:58

2 answers

4

There are several alternatives, I will 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

2

The use of the row_number() function is really the traditional approach, for version 2008. The definition of partitioning in OVER() was perfect, also placing the COMPANY column, even if in the example there is only one company. // At first it would be recommended to put the data reading filters* in the CTE, as it can return fewer lines in the CTE. However, the query optimizer is smart enough to do this on its own. // (*) In this case, it would be to move the COMPANY filter = 1 into the TEC

– José Diz

2017/07/08 at 11:59

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.

by Elton C Ramos • 1 point · Answer 1 · 2017-07-10T21:29:20+00:00

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 it is Oracle it 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;

whereas DTFINAL be a key to your table, you say you want to field DTFINAL the longest date (for the same keys) . So use the MAX and make a Join with the subselect table. The SYSDATE you say the highest date has to be less than or equal to the current date.