0

I have this table:

enter image description here

I want to make a query that allows me to show the last value in fecha_gestion grouped by NitSec. It would be something like this:

SELECT id_log, fecha_gestion, nitsec 
FROM table 
GROUP BY nitsec 
ORDER BY fecha_gestion DESC

The result I want is this:

ID_log | fecha_gestion    | nitsec  
6      | 2020-01-03 09:52 | 5443
11     | 2020-01-03 10:12 | 1 
15     | 2020-01-03 10:23 | 3286

After doing this I need an inner join (but I can do it easily). Thanks in advance.

0
1

One possible approach is to number rows appropriately using ROW_NUMBER():

SELECT id_log, fecha_gestion, nitsec
FROM (
   SELECT 
      id_log, fecha_gestion, nitsec,
      ROW_NUMBER() OVER (PARTITION BY nitsec ORDER BY fecha_gestion DESC) AS rn
   FROM table 
) t
WHERE rn = 1
1
  • 1
    Thank you. That was the perfect match Jan 7 '20 at 13:24
1

Because no sample data was given, the next query is untested:

SELECT
    ID_log,
    fecha_gestion,
    nitsec
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.nitsec = t1.nitsec and t2.fecha_gestion < t1.fecha_gestion)

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.