last inserted or modified rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

last inserted or modified rows in a table

Author

Topic

barcelo
Starting Member

20 Posts

Posted - 2013-10-24 : 16:53:36

how I get 10 last rows inserted or modified in a table?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 17:24:38

quote:
Originally posted by barcelo

how I get 10 last rows inserted or modified in a table?

If you have something like a timestamp in your table or have some kind of auditing enabled, you would be able to query that to get such information.

Otherwise, there is no built-in feature that will allow you to get last inserted or modified rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 03:33:52

otherwise if you've Primary Key sequential column (ie with IDENTITY or NEWSEQUENTIALID property) you can us this


SELECT TOP 10 *
FROM Table
ORDER BY SequentialColumn DESC

or if you want to capture it at insert time, use OUTPUT clause and get contents from INSERTED table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-25 : 09:32:30

Example of my table:

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..4/10/2013
21866........2......12:55..17:01..4/10/2013
05214........1......08:05..12:15..4/10/2013
05214........2......13:00..17:08..4/10/2013

when a new row is created, just the columns (Id_Employee, Order, IN, Date) contain data, the column "OUT" is NULL/Clear yet.

Then if I run:
SELECT TOP 10 Date, Id_Employee, IN, OUT
FROM Employee
WHEN Order='1'
ORDER BY Date desc

I get the las rows inserted, but not the last rows with column OUT changued.

:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 09:34:26

hmm..where are you changing OUT column? your explanation just says its NULL during insertion. then how do you expected rows to get their OUT values changed? do you've some trigger ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-25 : 09:50:59

quote:
Originally posted by barcelo

Example of my table:

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..4/10/2013
21866........2......12:55..17:01..4/10/2013
05214........1......08:05..12:15..4/10/2013
05214........2......13:00..17:08..4/10/2013

when a new row is created, just the columns (Id_Employee, Order, IN, Date) contain data, the column "OUT" is NULL/Clear yet.

Then if I run:
SELECT TOP 10 Date, Id_Employee, IN, OUT
FROM Employee
WHEN Order='1'
ORDER BY Date desc

I get the las rows inserted, but not the last rows with column OUT changued.

:(

Given the input data that you have posted, what is the output you would like to get?

barcelo
Starting Member

20 Posts

Posted - 2013-10-25 : 10:16:08

quote:
Originally posted by James K

quote:
Originally posted by barcelo

Example of my table:

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..4/10/2013
21866........2......12:55..17:01..4/10/2013
05214........1......08:05..12:15..4/10/2013
05214........2......13:00..17:08..4/10/2013

when a new row is created, just the columns (Id_Employee, Order, IN, Date) contain data, the column "OUT" is NULL/Clear yet.

Then if I run:
SELECT TOP 10 Date, Id_Employee, IN, OUT
FROM Employee
WHEN Order='1'
ORDER BY Date desc

I get the las rows inserted, but not the last rows with column OUT changued.

:(

Given the input data that you have posted, what is the output you would like to get?

What I need is to get the 10 last rows inserted or modified,
example:
Result:
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..25/10/2013...<------when updated OUT
05214........1......08:05..12:15..25/10/2013...
05214........1......12:55.." "..25/10/2013...
21866........1......13:00.." "..25/10/2013...<------ When Inserted
21866........1......07:55..17:01..24/10/2013
21866........1......12:55.." "..24/10/2013

barcelo
Starting Member

20 Posts

Posted - 2013-10-25 : 13:37:28

How I can do something like this in sql server with the columns IN and OUT as INSERTED and UPDATED?

"If you need to get all row recently inserted or updated, I sugest to add INSERTED and UPDATED field with date time data type. So you can select your table with recently interval. See sample bellow:

SELECT *, INSERTED, UPDATED
FROM YOUR_TABLE
WHERE INSERTED BETWEEN (NOW() - INTERVAL 1 MINUTE) AND NOW()
OR UPDATED BETWEEN (NOW() - INTERVAL 1 MINUTE) AND NOW();"

http://stackoverflow.com/questions/12892388/how-to-return-all-last-rows-fields-of-the-last-inserted-updated-row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 08:01:08

do you mean this?


SELECT *
FROM YOUR_TABLE 
WHERE ([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 10:02:24

quote:
Originally posted by visakh16

do you mean this?

SELECT *
FROM YOUR_TABLE
WHERE ([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

yes Exactly that, but I tried it and it is not what I need.
As I said before, what I need is to get a table, the last 10 rows inserted or modified.

In the case of the Employee table with Order 1 Whenever you create a row the column OUT is blank up, and then is updated by sending out data to column OUT

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..4/10/2013 ///Updated added data in column OUT
21866........1......12:55.." "..4/10/2013 ///Inserted column OUT is empty

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:23:37

[code]
SELECT TOP 10 *
FROM YOUR_TABLE
WHERE ([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
ORDER BY COALESCE([OUT],0) DESC, [IN] DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 13:41:47

quote:
Originally posted by visakh16

SELECT TOP 10 *
FROM YOUR_TABLE
WHERE ([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
ORDER BY COALESCE([OUT],0) DESC, [IN] DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

good! :) but I get data of Order 1 and Order 2

I need to get the data separately as

...
...WHERE Order='1'

I tryed:

SELECT TOP 10 *
FROM YOUR_TABLE
WHERE Order='1' AND
([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
ORDER BY COALESCE([OUT],0) DESC, [IN] DESC

but the result is not good...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:48:03

Why dont you show us some sample data to give us an idea of what you're after?
Why to keep us guessing like this which will only delay your chances of getting correct result.
Here's my next interpretation.


SELECT Id_Employee,[Order],[IN],[OUT],[Date]
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY COALESCE([OUT],0) DESC, [IN] DESC) AS Seq
FROM YOUR_TABLE 
WHERE ([IN] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
OR ([OUT] BETWEEN DATEADD(minute,-1,GETDATE()) AND GETDATE())
)t
WHERE Seq <= 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources