Do SELECT with 20 different Monsters IDs in SQL server 2008 R2

Question

Do SELECT with 20 different Monsters IDs in SQL server 2008 R2

Navigation

#1 by (2 votes)

0

I have the following SQL server structure:

ROW    Monstruo MobID Fecha
1      Feo      4001  14-12-2016 22:22:03
2      Horrible 4002  12-12-2016 22:22:03
3      Espanto  4003  11-12-2016 22:22:03
4      Espanto  4003  17-12-2016 22:22:03
5      Tesoreo  4004  12-12-2016 22:22:03
6      FEO      4001  11-12-2016 22:22:03

I need to make a SQL Server SELECT query that captures the monsters by MobID by a more recent date OMITTING the older dates and without taking the repeated IDS Mobs EXAMPLE that the SELECT result should have

ROW    Monstruo MobID Fecha
1      Feo      4001  14-12-2016 22:22:03
2      Horrible 4002  12-12-2016 22:22:03
4      Espanto  4003  17-12-2016 22:22:03
5      Tesoreo  4004  12-12-2016 22:22:03

IMPORTANT the registry has more than 1000 records and of which there are more than 5000 different MobIDs that go from MobID 1 to 8035 with their different names. This example above was a small scale example.

sql sql-server

asked by Juan Carlos Villamizar Alvarez 30.12.2016 в 16:20

source

1 answer

How do I change my user authentication to another column in the db as username in laravel? Object values in ASP are not updated

score 2 · Accepted Answer

You can fill ROW_NUMBER :

WITH CTE AS
(
    SELECT  [ROW],
            Monstruo,
            MobId,
            Fecha,
            RN = ROW_NUMBER() OVER(PARTITION BY Monstruo ORDER BY Fecha DESC)
    FROM dbo.TuTabla 
)
SELECT [ROW],
        Monstruo,
        MobId,
        Fecha
FROM CTE
WHERE RN = 1;