TSQL Getting most recent record only

Question

i'm trying to get only the most recent location of an asset, I found this, following that example. I did this:

ALTER PROCEDURE [SISACT].[index_activo_ubicacion]
            @ubicado BIT = 1
AS
BEGIN
    IF @ubicado = 1
         SELECT activo,
               ubicacion
         FROM
         (SELECT activo,
          ubicacion,
          Fecha_Ubicacion,
          row_number() OVER (partition BY activo
                             ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate())))
          FROM [SISACT].ACTIVO_UBICACION)
    ELSE
     SELECT Activo FROM [SISACT].ACTIVOS_FIJOS a 
     WHERE NOT EXISTS (
                    SELECT Activo FROM [SISACT].ACTIVO_UBICACION u 
                    WHERE a.Activo = u.Activo
     )
END

But I'm getting an error sintax near "ELSE", meaning the error is here:

    SELECT activo,
           ubicacion
    FROM
    (SELECT activo,
          ubicacion,
          Fecha_Ubicacion,
          row_number() OVER (partition BY activo
                             ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate())))
     FROM [SISACT].ACTIVO_UBICACION)

but i have no idea what i'm doing wrong since I'm fairly new to TSQL. Can anyone help me?

In the table ACTIVO_UBICACION there will be many records with the same ACTIVO but different Fecha_Ubicacion, I want to select all ACTIVO but only the ones with the most recent date. Meaning

Activo | Ubicacion | Fecha_Ubicacion
------------------------------------
Activ  | 1244      | 2012-04-02 15:42:33
FixedA | 4556      | 2012-05-05 13:33:34
Activ  | 4553      | 2012-05-06 22:11:45
FixedA | 2S01      | 2012-07-10 12:44:20
Activ  | 1114      | 2013-03-14 14:33:45

Meaning it should return

Activo | Ubicacion | Fecha_Ubicacion
------------------------------------
FixedA | 2S01      | 2012-07-10 12:44:20
Activ  | 1114      | 2013-03-14 14:33:45

Thank you in advance

Have you tried running the two different SQL statements on their own to validate them first? I suggest divide and conquer - change one of the SQL statements to SELECT 1 and save the SP. Then change the other... then remove the IF. SP error messages are not usually that helpful and your code looks fine to me. — Nick.Mc, Apr 08 '13 at 01:47

Andriy M · Accepted Answer · 2013-04-09T05:06:53.010

The syntax error has to do with the alias of the first SELECT's subselect.

In Transact-SQL, a derived table must have an alias. Yours apparently doesn't, although SQL Server "thinks" that the subsequent ELSE may be the one. And since ELSE is a reserved keyword, the parser reports an error. So, the solution is to add an alias.

However, the row_number expression is left without a name too, which would be the next thing the parser would complain about after fixing the derived table's alias. Here are those two fixed, as well as one more suggestion put as a comment:

ALTER PROCEDURE [SISACT].[index_activo_ubicacion]
            @ubicado BIT = 1
AS
BEGIN
    IF @ubicado = 1
         SELECT activo,
               ubicacion
         FROM
         (SELECT activo,
          ubicacion,
          Fecha_Ubicacion,
          row_number() OVER (
            partition BY activo
            ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum
          FROM [SISACT].ACTIVO_UBICACION
         ) someAlias
         -- possibly you need this filter as well (uncomment it if so):
         -- WHERE RowNum = 1
    ELSE
     SELECT Activo FROM [SISACT].ACTIVOS_FIJOS a 
     WHERE NOT EXISTS (
                    SELECT Activo FROM [SISACT].ACTIVO_UBICACION u 
                    WHERE a.Activo = u.Activo
     )
END

Hello. Tried this but I get "No column name was specified for column 4 of 'alias' — Splendonia, Apr 09 '13 at 03:11
@Splendonia: So, have you tried specifying a name for it? It's the `row_number()` expression that was left without a name. Please see my update. — Andriy M, Apr 09 '13 at 05:06
Uncommented the filter. It works. Thank you very much for your response :) — Splendonia, Apr 09 '13 at 06:13

Michael Harmon · Answer 2 · 2013-04-08T02:36:22.817

1

How about?:

select a.Activo,
(select b.Ubicacion 
FROM ACTIVO_UBICACION b
WHERE b.Fecha_Ubicacion=max(a.Fecha_Ubicacion)) as Ubicacion, 
max(a.Fecha_Ubicacion) as Fecha_Ubicacion
from ACTIVO_UBICACION a
GROUP BY a.Activo

edited Apr 08 '13 at 02:36

answered Apr 08 '13 at 01:56

Michael Harmon

746
5
10

The other solution from Andriy M is much better than mine. Mine can bring back wrong data in the event that more than one record has the same Fecha_Ubicacion date. – Michael Harmon Apr 09 '13 at 14:40

TSQL Getting most recent record only

2 Answers2