Get The Last 100 Records

Oct 3, 2007

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 8 Replies


SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.

In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously

While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.

Is there any SQL Server hints to avoid blocking ..

View 3 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
SELECT 1,1,1.2


This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

Data Flow Task To Delete Records And Then Insert Records In Transaction

Aug 6, 2007


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 7 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006


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 3 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:


but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS

Jul 23, 2005

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 2 Replies View Related

Need Efficient Query To Partition Records By Type And Pull Top N Records From DB

Jan 18, 2008

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 4 Replies View Related

Insert 9900 Records Out Of 10000 Records Using DTS

Nov 28, 2005

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 1 Replies View Related

Looping Thru Records To Find Related Records

Oct 31, 2007

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 6 Replies View Related

Search The Records After The Records Populated

Aug 17, 2007


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 4 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

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 4 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table

Oct 21, 2015

I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.

Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY

[Code] ...

It's only returning two rows (the students where they have a row in the HomeworkLogLine table). 

View 3 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.


Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'


Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Multiple Records And Sub Records

Apr 15, 2003

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 9 Replies View Related

Displaying Records Associated With Records...

Jun 21, 2007

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 4 Replies View Related

Show Records That Have Sub-records?

Mar 5, 2007


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 6 Replies View Related

Select Top 25% Records,except For Top 25% Records

Aug 30, 2006

A basic select statement to be used.

View 3 Replies View Related

How Do I Select All Records In One Table That Have NO Related Records In Another Table?

Jul 20, 2005

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 2 Replies View Related

Copy Records From One Database To Another Database With Difference Of Records.

Apr 6, 2008

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 two databases with same tables with different records and 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) contains 6 records.
Database2 (EmployeeTable) contains 10 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 1 Replies View Related

Delete Records From A Table Using Records In Another Table.

Jul 20, 2005

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 7 Replies View Related

What If I Want Records Between 10-15?

Jun 6, 2008

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 4 Replies View Related

How To See If Last Five Records Have Same Value

Jan 6, 2015

Suppose I have a table called Order that stores an OrderID, CustomerID, OrderType and OrderDate. How could I write a query to return a list of customers whose five most recent orders (based on the OrderDate) feature the same OrderType?

If only four of the five most recent for a given customer feature the OrderType, then none of them would be returned.

View 5 Replies View Related

Get Records ...need Some Help...

Jan 5, 2007

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 1 Replies View Related

Min Records

Jul 9, 2007


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 6 Replies View Related

500,000+ Records

Nov 14, 2007


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 20 Replies View Related

Records And No Records

Jan 24, 2008

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 5 Replies View Related

Sum Of Records

Jan 26, 2008

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 5 Replies View Related

How To Get This Records?

Dec 3, 2007

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 4 Replies View Related

All Records In Only One Row, Why?

Mar 4, 2008

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 3 Replies View Related

First N Records

Jun 27, 2007

How can I select only the first n records from a table ?


Ciornei Mihai

View 5 Replies View Related

Copyrights 2005-15, All rights reserved