CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Splendonia
Splendonia

Reputation: 1369

TSQL Getting most recent record only

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

Upvotes: 0

Views: 415

Answers (2)

Andriy M
Andriy M

Reputation: 77697

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

Upvotes: 2

Michael Harmon
Michael Harmon

Reputation: 746

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

Upvotes: 1

Related Questions