Sql Server Row Date Last Modified

create table users(user_id integer, name varchar(10),   email varchar(20),  modified_date timestamp);

insert into users 
values
(1  ,'John',    'a@gmail.com',  '2022-01-01'),
(1  ,'John',    'b@gamil.com',  '2022-02-01');

select user_id, email, name
from
(
SELECT user_id, name, email,row_number() over(partition by user_id order by modified_Date desc) as rnk
FROM users
  ) as t
  where rnk = 1;

user_id	email	name
1	b@gamil.com	John

How do I add a “last modified” and created column in a SQL Server table?

The created column is simple - just a DATETIME2(3) column with a default constraint that gets set when a new row is inserted:

Created DATETIME2(3) 
   CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())

So when you insert a row into YourTable and don't specify a value for Created, it will be set to the current date & time.

The modified is a bit more work, since you'll need to write a trigger for the AFTER UPDATE case and update it - you cannot declaratively tell SQL Server to do this for you....

Modified DATETIME2(3)

and then

CREATE TRIGGER updateModified
ON dbo.YourTable
AFTER UPDATE 
AS
   UPDATE dbo.YourTable
   SET modified = SYSDATETIME()
   FROM Inserted i
   WHERE dbo.YourTable.PrimaryKey = i.PrimaryKey

You need to join the Inserted pseudo table which contains all rows that were updated with your base table on your primary key for that table.

And you'll have to create this AFTER UPDATE trigger for each table that you want to have a modified column in.

SQL Server database last updated date time

Look in sys.objects should be enough, try this query

 select * from sys.objects
order by modify_date desc

Find the last time table was updated

If you're talking about last time the table was updated in terms of its structured has changed (new column added, column changed etc.) - use this query:

SELECT name, [modify_date] FROM sys.tables

If you're talking about DML operations (insert, update, delete), then you either need to persist what that DMV gives you on a regular basis, or you need to create triggers on all tables to record that "last modified" date - or check out features like Change Data Capture in SQL Server 2008 and newer.

Select only rows with last modified timestamp, without duplicate rows with same ID and older timestamp

with x as (select o.modified, od.* 
           from orderDetails od, orders o
           where o.id=od.order_id)
 , mx as (select max(modified) as modified, detail_id
          from x group by detail_id)
Select x.* from x, mx
Where x.detail_id = mx.detail_id and x.modified=mx.modified

Here we use Common Table Expressions, so we join the two tables only once.
At least we did it only once when writing the query - thus we would have less chances to do typo or copy-paste errors.
We also hinted the SQL server to do the join only once and then to reuse it, but would it follow this hint or not - depends on its internal implementation.

Another good thing about CTE: it helps you to build your query incrementally, step by step, from simple to complex. Read about Read–eval–print loop at https://en.wikipedia.org/wiki/REPL

I would add a bit more of it later.

You can find many articles about CTE in Google. The Firebird implementation is documented here: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-cte

Since I used only very basic SQL I believe it to work in almost any practical SQL server, Firebird including.

Here is the result of the query and the output data: SQL Fiddle

PostgreSQL 9.6 Schema Setup:

create table orders
 (id integer primary key,
  modified timestamp);
create index o_m on orders(modified);  

create table OrderDetails(
  order_id integer references orders(id),
  detail_id integer not null,
  base_price float,
  buy_price float,
  sell_price float );
create index od_do on OrderDetails(detail_id, order_id);

Insert into orders values
( 1, '2018-1-07'),
( 2, '2018-1-10'),
( 3, '2018-1-15'),
( 4, '2018-1-20'),
( 5, '2018-1-25');

Insert into OrderDetails values
(   1   ,   1   ,   99.00   ,   111.00  ,   122.00  ),
(   1   ,   2   ,   82.00   ,   95.00   ,   117.00  ),
(   1   ,   3   ,   82.00   ,   95.00   ,   117.00  ),
(   2   ,   4   ,   95.00   ,   108.00  ,   119.00  ),
(   2   ,   5   ,   86.00   ,   94.00   ,   115.00  ),
(   2   ,   1   ,   82.00   ,   95.00   ,   117.00  ),
(   3   ,   1   ,   92.00   ,   106.00  ,   116.00  ),
(   3   ,   4   ,   90.00   ,   100.00  ,   120.00  ),
(   3   ,   5   ,   82.00   ,   95.00   ,   117.00  ),
(   4   ,   2   ,   92.00   ,   106.00  ,   116.00  ),
(   4   ,   3   ,   90.00   ,   100.00  ,   120.00  ),
(   4   ,   1   ,   82.00   ,   95.00   ,   117.00  ),
(   5   ,   1   ,   92.00   ,   106.00  ,   116.00  ),
(   5   ,   5   ,   90.00   ,   100.00  ,   120.00  ),
(   5   ,   3   ,   82.00   ,   95.00   ,   117.00  );

Query 1:

with x as (select o.modified, od.* 
           from orderDetails od, orders o
           where o.id=od.order_id)
 , mx as (select max(modified) as modified, detail_id
          from x group by detail_id)
Select x.* from x, mx
Where x.detail_id = mx.detail_id and x.modified=mx.modified
Order by detail_id

Results:

|             modified | order_id | detail_id | base_price | buy_price | sell_price |
|----------------------|----------|-----------|------------|-----------|------------|
| 2018-01-25T00:00:00Z |        5 |         1 |         92 |       106 |        116 |
| 2018-01-20T00:00:00Z |        4 |         2 |         92 |       106 |        116 |
| 2018-01-25T00:00:00Z |        5 |         3 |         82 |        95 |        117 |
| 2018-01-15T00:00:00Z |        3 |         4 |         90 |       100 |        120 |
| 2018-01-25T00:00:00Z |        5 |         5 |         90 |       100 |        120 |

Note, it would have different output if you would have two or more orders with the same timestamp! It seems you did not even thought about this possibility - but since it is possible, it would eventually happen.

Now, Back to CTE and REPL.

As you incrementally build your query, from the first vague idea to specific rows, it would be nice to check that the output data is exactly what you expect. "Big elephant it better to be eaten by small pieces".

And here I will show you the step by step building of the query.
It would be useful if you would repeat those steps in the SQL Fiddle linked above.

First of all I created and populated the tables.

Then I issued the first queries just to check I did populated them correctly.

1: select * from orders - try this and further queries in SQL fiddle (or in IBExpert, FlameRobin, etc

2: select * from orderDetails

3: Then I issued the joining query to check if my cross-tables query really gives meaningful output. It did.

select o.modified, od.* 
from orderDetails od, orders o
where o.id=od.order_id

4: Then I wondered, can I get the last timestamp for detail out of that query? To check it what I did was the following: 1) saved the aforementioned query I did and tested earlier, and 2) wrote a secondary query on top of it. It did extracting the last change date okay. Written and tested.

with x as (select o.modified, od.* 
           from orderDetails od, orders o
           where o.id=od.order_id)
Select max(modified) as modified, detail_id
  from x group by detail_id

5: And the last step was saving the test secondary query too, and writing the final, tertiary query on top of them both, giving the final filtered data

---

Yet more efficient solution can be using the one-run join query (the one I introduced above in Step 3. and saved as x) with adding order by detail_id, modified desc and then using Window Functions introduced in Firebird 3.

Here is the answer to a similar question using that approach - Firebird select from table distinct one field

Window Functions are not available in Firebird 2.x though.

Optimized SQL query for getting last modified date of a user information

If I understood the question correctly, you want the latest modification date for each user. Which boils down to a greatest-n-per-group problem (with n=1). And they are typically solved using distinct on () in Postgres:

select distinct on (usr.id) id, last_modified_date 
from 
(
  select id, last_modified_date 
  from "User_Details" 

  UNION ALL

  select user_id as id, last_modified_date 
  from "User_Contact_Details" 

  UNION ALL

  select user_id as id, last_modified_date 
  from "User_Social_Media_Details" 
) as usr 
where last_modified_date is not null
order by id, last_modified_date desc;

The order by inside the union queries is not really necessary, unless you want to pre-filter the user IDs in there already, which might be more efficient:

select distinct on (usr.id) id, last_modified_date 
from 
(
  select distinct on (id) id, last_modified_date 
  from "User_Details" 
  order by id, last_modified_date desc

  UNION ALL

  select distinct on (user_id) user_id as id, last_modified_date 
  from "User_Contact_Details" 
  order by user_id, last_modified_date desc

  UNION ALL

  select distinct on (user_id) user_id as id, last_modified_date 
  from "User_Social_Media_Details" 
  order by user_id, last_modified_date desc

) as usr 
where last_modified_date is not null
order by id, last_modified_date desc;

You still need the distinct on () in the outer query because the same user ID could be returned from the different branches of the UNION.

---

user is a reserved keyword, avoid using it as an identifier. And if you do, you have to quote it "user" to avoid confusion with the built-in function user

---

If the "User_Details" table is the "master" table, referenced by the others and you just want to get the latest modification date per user id, regardless in which table that occurred, you could also use a join with a group by:

select id, 
       max(greatest(ud.last_modified_date, ucd.last_modified_date, usmd.last_modified_date)) as latest_modification
from "User_Details" ud
  left join "User_Contact_Details" ucd on ucd.user_ud = ud.id
  left join "User_Social_Media_Details"  usmd on usmd.user_id = ud.id
group by id;

Date object last modified

Note that SQL Server actually does not record the last modification date. It does not exist in any system tables.

The Schema Changes History report is actually constructed from the Default Trace. Since many admins (and web hosts) turn that off, it may not work for you.
Buck Woody had a good explanation of how this report works here. The data is also temporary.

For this reason, you should never RELY on the Schema Changes History report. Alternatives:

• Use DDL Triggers to log all schema modification to a table of your choosing.
• Enforce a protocol where views and procs are never altered, they are only dropped and recreated. This means the created date will also be the last updated date (this does not work with tables obviously).
• Vigilantly version your SQL objects and schema in source control.

--

Edit: saw that this is SQL 2000. That rules out Default Trace and DDL Triggers. You're left with one of the more tedious options I listed above.

Getting the most recent Modified Date older than 7 days per record with multiple dates

Try adding a having clause

SELECT USERID
FROM [PROJ]
INNER JOIN [NOTES] ON [PROJ].id = [NOTES].projID
WHERE [PROJ.STATUS] = 'ACTIVE'
  AND [NOTES.MODIFIEDDATE] <= GETDATE() - 7 
GROUP BY USERID
HAVING MAX([NOTES.MODIFIEDDATE])

EDIT:
If you only want the most recent then I would get rid of the entirely

AND [NOTES.MODIFIEDDATE] <= GETDATE() - 7 

SELECT USERID
    FROM [PROJ]
    INNER JOIN [NOTES] ON [PROJ].id = [NOTES].projID
    WHERE [PROJ.STATUS] = 'ACTIVE'
    GROUP BY USERID
    HAVING MAX([NOTES.MODIFIEDDATE])

EDIT2:

SELECT USERID
    FROM [PROJ]
    INNER JOIN [NOTES] ON [PROJ].id = [NOTES].projID
    WHERE [PROJ.STATUS] = 'ACTIVE'
      AND [NOTES.MODIFIEDDATE] <= GETDATE() - 7 
    GROUP BY USERID, PROJECT
    HAVING MAX([NOTES.MODIFIEDDATE])

---

Related Topics