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

Aug 30, 2006



A basic select statement to be used.

View 3 Replies


ADVERTISEMENT

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

Nov 2, 2006

Hello

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

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 .....how 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

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

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 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
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

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

HI,

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

Scenario:
--------------
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 ,
Sunil

View 7 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
FROM RPL
ORDER BY 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:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

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()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
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

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

Select Records

Apr 30, 2007

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

Select 11-20 Records?

Jul 14, 2007

 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
Quote:

View 3 Replies View Related

How To Select Last 10 Records

Jan 12, 2006

I have a simple table something like this
ID    First        Last
1      Bob       Hope2     Charles   Draw3     Mark     Andrews
Etc, etc
I need the SQL statement select the last 10 records in the table, so if the highest ID id 82 I need all fields of records with ID 72-82
Help appreciated, i'm sure it must be simple enough buy mind is blank.
thanks
 

View 1 Replies View Related

Select 11-20 Records?

Jul 14, 2007

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
Quote: SELECT TOP 10 * FROM table WHERE id > 10
because even though my ID is auto numbered, but I have deleted some entries.
Does anyone have good solutions?

p.s I tried this and doesn't work either
Quote: SELECT TOP 10 * FROM table WHERE id IN(SELECT TOP 20 * FROM table ORDER by date DESC) ORDER BY date ASCENDING
TIA
-noPcz

View 5 Replies View Related

Select Records

Aug 16, 2005

Stijn writes "Hello,

I have SQL 7 with WINDOWS 2000 SERVER

My problem is that i am looking for a script

my db is for equipement inventory

how can i select all the records from a room,
if this room (room1)has less of 100 records he needs te go to the follow room(room2), en the next room until he's got 100 records,

sorry for my english"

View 2 Replies View Related

How To Select Last Few Records

Aug 18, 2005

Mack writes "I want to select last 2 records from a table AA which has following 5 records.

111
222
333
444
555

There is no primary key.
Any tip will be really appretiated.
Thanks for taking time to read it."

View 2 Replies View Related

Select Top 50 Records

Oct 4, 2007

Hi,
How to write a query to get top 50 distinct records from a table.
I mean I have a table 'C1_Subscribers' with a field name 'Nickname'. Here I need to select top 50 non-repeated nickname only.

Can anybody can help me!!

View 3 Replies View Related

SELECT-how To Get Only The Last 20 Records?

Jul 23, 2005

Hello,Firstly, sorry for my english.I have problem with creating SQL statement. I am beginner and I think thatit is very easy to do. Look -I have to get only the last 20 records from table ABC (f.eg.) and accordingto that how to create the SQL statement? F.eg. SELECT name, forename, dateFROM abc WHERE....You know - how to finish the statement to get only 20 records lastlyinserted into the table?Thanks and I am waiting for helpfull answers.

View 3 Replies View Related

Select Top N Records With A Certain Value

Jan 2, 2008

I have a table, productTable, that contains a ProductID column, a bunch of attribute columns like manufacturer etc, and a GroupID. Many different productIDs can share the same GroupID which means that they are basically the same product with one different attribute such as color. What I'm trying to do is select all the products in the TOP N GroupIDs. I'm using this to paginate my query results. I tried this which does not work:

SELECT DISTINCT TOP 10 *
FROM productTable
WHERE GroupID NOT IN
(SELECT DISTINCT TOP 10 GroupID
FROM productTable
ORDER BY GroupID)
ORDER BY GroupID

This returns the TOP 10 rows, but I would like all the ProductIDs for the TOP 10 GroupIDs

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

Select Records. Going Crazy.

Apr 29, 2007

Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE)  2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables:     Articles and Comments associated with those articles2. Create 2 Stored procedures:    The first one outputs the articles.    The second output all comments given an article ID    In this case, while the data is being displayed on the page it will load the comments for each article.    The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel  

View 4 Replies View Related

Select Distinct Records

Oct 10, 2007

 Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid   Description 1     T1 2     T2 So I need the distinct values. How to solve this? Thanks, Miguel

View 1 Replies View Related

Select Count(*) For Getting # Of Records

Nov 23, 2007

Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()
 
I am getting an error that that's no data in the table.
Any suggestions?

View 1 Replies View Related

Select All Records Between 2 Dates

Apr 6, 2004

I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.

The asp.net code I am trying to use is:


Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"

....

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim startdate As DateTime
startdate = Request.Form("date_from")

Dim enddate As DateTime
enddate = Request.Form("date_to")

SqlDataAdapter1.Fill(DataSet1)
Repeater1.DataSource = DataSet1
Repeater1.DataBind()

End Sub


With this I am getting the following error:

"Syntax error converting datetime from character string. "

So I am assuming it is something to do with the way I am getting the date from the form as when I hardcode the dates in it works???

Any help would be greatly appreciated, thanx

View 1 Replies View Related

How To List The Records Of A Select

Nov 14, 2005

Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid

View 1 Replies View Related

How To SELECT The Latest Records?

Sep 21, 2007

Hello!

I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.

With Sybase (a not so distant cousin of MS SQL) the following works:

select * from TABLE having date = max(date)


With MS SQL, however, the same query does not work:

Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


What's the solution? Thanks!

View 14 Replies View Related

Select Top Needs To Return 2 Records Only

Jan 10, 2013

I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.

id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231

I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query

select sum(series), date from table group by date order by sum(series) desc

This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.

View 1 Replies View Related

Select 10 Random Records

Apr 22, 2008

Hello,

Is it possible to select 10 random records from a table?

Thanks,
Miguel

View 1 Replies View Related

Select Two Records With Same Id,different Logflag

Jun 11, 2008

Hi guys

I have a table that stores records and each receord has a default logflag value of 0.Each time any record is changed,a new record is inserted with similar vaues but logflag flag remains 0.The original record that was modified will is kept but will have its log flag set to 1.

Now i want to display the two records in a report one on top of the other so that users can clearly see what chages were made for audit purposes.

How do i do my select statement so that for each record that was modified,the original one(logflag 1) is displayed and the new modified one(logflag 0 ) is also displayed just below it.

ie

id name email address city logflag modifieddate

23 rob rob@me.com oxford lane wolverton 1 20/6/2008

23 rob robe@me.com huge road burnside 0 null

The same should be done for all the modified records.

When a record is has not been modified,it should not be displayed by the select statement.

Thanks in advance


In god we trust,everything else we test.

View 4 Replies View Related

Select Only Records That Do Not Exist

Jan 24, 2014

I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting is

select distinct qf.[eform_id]
, qf.[name]
, qf.[description]
from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf
inner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_default
where not exists (select fr.eform_id
from forms_ref fr
inner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id);

It is outputting nothing, but I have intentionally deleted one form record to force it to show up.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved