Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Get The Last 100 Records

My sql database table gets filled automatically.

Every record gets a current date/time stamp. 

I want to select the last 100 records, ordered by the date/time stamp.

The newest records should be the last record in the 100 recordset.

How can I do this?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Data Flow Task To Delete Records And Then Insert Records In Transaction

I have been trying to solve the locking problem from past couple of days. Please help mee!!

I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,

View Replies !   View Related
I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.

View Replies !   View Related
Need Efficient Query To Partition Records By Type And Pull Top N Records From DB
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End') based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.

1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.

;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC

Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?

Thanks in advance.

View Replies !   View Related
Insert 9900 Records Out Of 10000 Records Using DTS
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.

View Replies !   View Related
Looping Thru Records To Find Related Records
Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data

Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17

If anyone could help it would be much appreciated.

View Replies !   View Related
HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each &&"cell&&"

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix

test a

text b

text c

text d

text e

text f

text g

This is a matrix with all the same row-height.

test a

text b


text c


text d

text e

text f

text g



Thx you a lot

View Replies !   View Related
Search The Records After The Records Populated


I have to search the records after the records populated.

I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.

For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.

Please tell me how to implement in this report,

Thanks and Regards
Altaf Nizamuddin

View Replies !   View Related
How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View Replies !   View Related
Multiple Records And Sub Records
I'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(

I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.

I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.

Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?

Thank you very much for any help,

View Replies !   View Related
Displaying Records Associated With Records...
I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:

This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).

Here are the fields and some test data in each of the two important tables:

tblDepartment =============================

tblRequest ========================================

What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):


We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.

Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!

View Replies !   View Related
Show Records That Have Sub-records?

I have one table which holds an ID, a name, and a parentID (which can either the same as the main ID or it's one of the other ID in that tbl)

At the moment I do a select on the records whos main ID is the same as the ParentID. This gives me an initial list of records who are the "Main" parent records (i.e they are not the child of any other record..

so far so good.

However, what I need to do is for each record work out if they themselves have any Child records. (only some have).

so at the mo I end up with a results set as this:

1 Boats

2 Jumpers

3 Trousers

4 Ships

What I want to end up is something like

1 Boats True

2 Jumpers False

3 Trousers True

4 Ships True

Where the true or false is if the record has any child records..

Any ideas?

View Replies !   View Related
How Do I Select All Records In One Table That Have NO Related Records In Another Table?
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks

View Replies !   View Related
Copy Records From One Database To Another Database With Difference Of Records.
I already submitted this type of question before and i receive reply. But unfortunately i found out errors when performing on my system.

My problem regarding to this one:

Suppose i have twodatabases with same tables with different recordsand I would like to copy the records from one database to another data and vice-versa. So that both the tables contains same number of records inside the tables.


Database1 (EmployeeTable) contains6 records.
Database2 (EmployeeTable) contains10 records.

It should copy only those records which is not present in each other database. No duplicate records.

Before i was recommend to use Primary key, if it is not present use index.

Hope this time i could solve my problem.


Kashif Chotu

View Replies !   View Related
Delete Records From A Table Using Records In Another Table.
I have been searching many postings and I cant seem to find anyonethat has this answer so I decided to post. I am using SQL(Transact-SQL).If I have 2 tables with columnsacct_num,activity_date,and pay_amt and I want to delete one instanceof a record in table 1 for every instance of that record in table 2how could I do that. For example.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00Table 2-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00I need a delete statement that will find 2 of the 5 records(It doesn'tmatter which 2) and delete them.Leaving table one looking like this.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00How can I do this??

View Replies !   View Related
What If I Want Records Between 10-15?
This query works fine if i want last five or first five records.
string qry = "select top 5 title,pid from pages where sid= '"+ sid +"' ORDER BY pid desc";
what query would be for that?

View Replies !   View Related
First 5 Records
does any one know how to grab only the first 5 records
if the following query "select firstname from employees" returns 1200 records.

I want to do this thru a sql stament, not with ADO.

View Replies !   View Related
Max No.of Records!
what is the maximum no.of records that can be stored in MS-Sql 7.0?

View Replies !   View Related
From Different Records To One

my table like below


when I select them with sql statement (e.g. select * from tbl)

one of them has different record line

but I want to get them in only one column


seperated comma or another one

is this possible ?

View Replies !   View Related
Sum Of Records
Dear all,
I need help from you,

I have a table named Messages with fields
Id (numeric)
Mobilenumber (varchar)
SmsBody (varchar)
MsgCnt (int)

Now here how to select all the records those have count of MsgCnt is greater than 10 for unique mobilenumber. It means in the table we can store number of records with same mobile number, but I need to see the records for unique mobilenumber where sum of MsgCnt is greater than 10


View Replies !   View Related
Records And No Records
Please, help. I have two tables, each with a field called "Status". I'm trying to write a ColdFusion/SQL query that lists records from TableA where TableA.Status is 1, AND there are NO records in TableB where TableB.Status is 1. This is probably simple to many of you, but I have no SQL training, so I'm begging for help. Here's as much as I can do (I think)...

<CFQUERY NAME="queryname" DATASOURCE="datasource">
SELECT TableA.Status, TableB.Status
FROM TableA, TableB
AND TableA.Status = 1


View Replies !   View Related
Min Records

got a sql qry that is suppose to pick minmum PRECISEIDSCORE one by one from the table. The table could have 3 records with the same minimum PRECISEIDSCORE, but I will need to pick one at a time. Here is the qry but is not working and not sure what is the problem. Please, any suggestions




View Replies !   View Related
Get Records ...need Some Help...
Dear my experts
I have a table called tbl_ShoppingCart with these fields

CartID,ProductID,ProductName, CustomerName,Ownedby

with OwnedBy is an email of person who has product.Example
ProductName= Name1;



My problem is: I want to get the records to send to the OwnedBy with their products. How can I query these fields..Thanks alot.
I am a beginner...

View Replies !   View Related
500,000&#043; Records

I have been playing with express version and a
very large table over 500,000 records. I noticed
that Express seems to respond very sluggish.

Can anyone advise on settings for using large
tables or is it better to use the full version

Any insight would be great..


View Replies !   View Related
All Records In Only One Row, Why?
I configured my output file as fixed width. Each column of the file haves the same OutputColumnWidth and the InputColumnWidht. The problem is that the rsults must be like 10 records in the file but all of them are in the same row.
So, I have all my records in a long row. I want to have each record in a different row.
Do you know how can I solve this?
Thanks for your help.


View Replies !   View Related
How To Get This Records?

2007-11-30 07:39:30.037
2007-11-30 07:39:31.037
2007-11-30 07:39:32.037
2007-11-30 07:39:33.037

How can i select all records inserted in 2007-11-30 ?.

This is wrong.....
select * from table
Where Rec_date = CONVERT(datetime, '11/30/2007', 121)

What is the correct syntax?.

View Replies !   View Related
First N Records
How can I select only the first n records from a table ?


Ciornei Mihai

View Replies !   View Related
Delete Records ???
hello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?

View Replies !   View Related
Distinct Records
i want to ask a simple question,
i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews).
the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'??

View Replies !   View Related
Accessing 2nd, 3rd And 4th To Last Records
Hey All
I wish to display the 3rd and 4th to last records of a table on a page. Is there anyway of doing this? I can access the last records by using Count and TOP 1 but i find that count - 1 as the ID to select can be errornous if records are removed from the table. I also tried using ORBER BY followed by LIMIT but kept getting an error in order by clause message.
Any help would be greatly appreciated
Thanks and Regards

View Replies !   View Related
Getting Total Of Different Records
I have a SQL data source and i would like to present the total number of different records based on a "status" field.
I have done total records in the past by doing this:
     protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)    {        int RecordCount = e.AffectedRows;            if (RecordCount == 1)            { litRecordCount.Text = "1 record found"; }            else            { litRecordCount.Text = RecordCount.ToString() + " records found"; }        }
This would work, howerver, my SQLdatasource choose * records, and not based on a WHERE condition. Is there any way to total just those with a status of "Initialized" ?
I tried to do it on the Gridview, but then I realized if I have paging on, it will only be on that front page.

View Replies !   View Related
Help With Count Records
Hello All,
 I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names.  I have tried this SQL statement in the SQL Express and it works very great.  
SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName
However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Can you please help me to fix this error? 
Thank you, Vic. 

View Replies !   View Related
While Loop Gets All Records Except First One
I'm doing a select from a database table, opening a data reader, then looping through the datareader in a while loop and setting a variable. For some reason every record gets stored in the variable except for the first one. Is there something you need to do in order to get the first one? Here is what I have. Thanks
2 Dim conn As New SqlConnection(Application("ConnectionString"))
3 conn.Open()
6 Dim cmdAllOffices As New SqlCommand("select officeid from corp_officephone_map_tbl where " & _
7 "emplid=" & "'" & strEmplid & "'", conn)
9 Response.Write(cmdAllOffices.CommandText.ToString & "<br><br>")
13 Dim drAllOffices As SqlDataReader = cmdAllOffices.ExecuteReader()
15 drAllOffices.Read() ' Read The Data
19 Dim strAllOffices As String = Nothing
22 While drAllOffices.Read()
25 strAllOffices &= ("'" & drAllOffices("officeid") & "'" & ",")
29 End While
strAllOffices contains a comma delimited string, but always is missing the first record.

View Replies !   View Related
Trying To Get Distinct Records
How do I get distinct TitleID and Titles?  Right now I'm still getting duplicates on them both.  Here's my stored procedure.
select Distinct (Titles.Titleid), Titles.Title as TITLE, classifications.[description]as TOPIC,Titles.descriptions,media.[description] as MEDIA
from  Titlesjoin resources on resources.Titleid = Titles.Titleidjoin media on media.mediaid = resources.mediaidjoin titleclassification on titleclassification.titleid = titles.titleidjoin classifications on classifications.classificationid = titleclassification.classificationid  WHERE Title LIKE 'p' + '%' GROUP BY Titles.titleid, titles.title,classifications.[description],Titles.[descriptions],media.[description] 

View Replies !   View Related
Get Records Count
i have this function
it return 0 but the sql statement in the sql query return the right number?how is that
i want to get the number of records any other idea or fix?
Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
dr = cmd.ExecuteReader()
dr.Read()userQuota = dr("rec_count").ToString
End Function

View Replies !   View Related
Most Matching Records
This is a where clause I am using in a search.
 WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' )
I am trying to do a search which returns the most matching record. For example if I have a record with Denver  as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return  matching results in a ranked order.

View Replies !   View Related
Naviagating To Next Set Of Records In MS SQL
I have a table with 1000 records.I want to select first 100 rows and then display it on a table.when i click on the next button it should navigate to next set of records.Please give sample code for this.

View Replies !   View Related
Get @@identity From Several Records
I was wondering if you can help.
I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.
What I need to do is after I have created these new records update a field in each of them.
Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?
 Ideally I would like to use @@identity and datareaders with
Mark :) 

View Replies !   View Related
Select 11-20 Records?
 Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use

View Replies !   View Related
Tell When Records Are Replicated
Is there anyway to tell when records have been replicated to another device from SQL Server? I realize I can see it by looking in the distribution table, but I want to do it from a different database, so I cant access the distribution database from that DB. Any help would be much appreciated.

View Replies !   View Related
Select Records
Hello,I am selecting some articles and some comments related with it:SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmailFROM Articles aINNER JOIN Users u ON a.AuthorID = u.UserIDINNER JOIN Comments c ON a.ArticleID = c.ArticleIDI have 2 problems which I am trying to solve:1. Comments table also have an AuthorId So for each comment I also want to join to Users table and get the author name and email. How can I do this?2. I want to select all Articles even if it has comments or not. Can I use Inner Join or should I use Left Join? Is Outer Join still available in SQL 2005?Thank You,Miguel

View Replies !   View Related
DELETE Records.
Hello, I have 3 tables with their columns as follows:   + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK   + Labels [LabelId] PK , [LabelName]   + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel

View Replies !   View Related
Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?
ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
SELECT   Product.ProductID, Title
(ProductCategory INNER JOIN
(Category INNER JOIN Department    ON Department.DepartmentID = Category.DepartmentID)
  ON ProductCategory.CategoryID = Category.CategoryID)
  ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1

View Replies !   View Related
Get The Recent Records
i have a datetime field in the post tables.
I would like to get the records within the latest 7 days.
Are there any functions for doing something like this?
my current query is something like
select * from post where creation_time ....
 Thank you

View Replies !   View Related
Delete Records ?
Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime
Then i have some code like this:  (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?

View Replies !   View Related

Copyright 2005-08, All rights reserved