SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 last inserted or modified rows in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barcelo
Starting Member

20 Posts

Posted - 10/24/2013 :  16:53:36  Show Profile  Reply with Quote
how I get 10 last rows inserted or modified in a table?

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 10/24/2013 :  17:24:38  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/25/2013 :  03:33:52  Show Profile  Reply with Quote
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
Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/25/2013 :  09:32:30  Show Profile  Reply with Quote
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.

:(





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/25/2013 :  09:34:26  Show Profile  Reply with Quote
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

Edited by - visakh16 on 10/25/2013 09:35:20
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 10/25/2013 :  09:50:59  Show Profile  Reply with Quote
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?
Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/25/2013 :  10:16:08  Show Profile  Reply with Quote
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

Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/25/2013 :  13:37:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/26/2013 :  08:01:08  Show Profile  Reply with Quote
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
Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/28/2013 :  10:02:24  Show Profile  Reply with Quote
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






Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/28/2013 :  13:23:37  Show Profile  Reply with Quote

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
Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/28/2013 :  13:41:47  Show Profile  Reply with Quote
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...

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/28/2013 :  13:48:03  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000