SELECT Top. Which Method Is Best?
Jul 20, 2005
Hi
SQL Server 7.0 using stored procedures.
I need to do a SELECT Top but I always need to find the record count
for the entire SELECT statement.
ID Name
-- ----
1 Abraham
20 Barrington
32 Callaway
54 Dennis
58 Emmett
If I do a SELECT TOP 3, I'll get the required records but get a
recordcount of 3.
I cant use the ROWCOUNT method either becuase the recordcount will
also be 3.
I can see two different options for getting back the information I
need:
1 - Run the select statement twice, first to retrieve the recordcount
and then with top to get the recordset
2 - Use a temp table to get the entire recordset and recordcount, and
then extract the Top N records
I'd like to know if anyone has any other suggestions. Currently, I'm
tempted to go for the second option....
Thanks
Sam
View 5 Replies
ADVERTISEMENT
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Dec 1, 2005
hiplz can any one tellHow should i use select method of SqlDatasourceplz show little code toothanks
View 1 Replies
View Related
Nov 19, 2007
Hi guys,
I'm just curious if there is a prefered method when it comes to SELECT statements that include multiple tables. I have two versions:
SELECT FirstName, LastName, ProductName, SalePrice
FROM SALES, PRODUCTS, CUSTOMERS
WHERE MONTH(SaleDate) = '2'
AND YEAR(SaleDate) = '2005'
AND SALES.ProductID = PRODUCTS.ProductID
AND SALES.CustomerID = CUSTOMERS.CustomerID;
SELECT FirstName, LastName, ProductName, SalePrice
FROM CUSTOMERS
INNER JOIN SALES ON CUSTOMERS.CustomerID = SALES.CustomerID
INNER JOIN PRODUCTS ON PRODUCTS.ProductID = SALES.ProductID
WHERE MONTH(SaleDate) = '2'
AND YEAR(SaleDate) = '2005';
Could you tell me if either of these methods are favored over the other or if there is another way of producing the same output that is used over both these methods.
Thank you in advance
Butterfly82
View 4 Replies
View Related
Oct 12, 2006
helloo
Can I use "like" in datatable.select method??
meaning:
Dim exp As String = "c.cDesc like " & txtSearch.Text & " + N'%' "
Dim rows() As DataRow = dtCenters.Select(exp)
knowing that txtSearch.text has unicode characters
View 1 Replies
View Related
Jul 28, 2015
I have a code that gives me the sample I am looking for, but only displays one field.
NuminHH is my field
7320EN_Var is my Table
Proof is the new table I want to create.
SELECT DISTINCT [NuminHH] INTO Proof
from 7320EN_Var
View 21 Replies
View Related
Feb 17, 2006
I have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?
View 1 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 Replies
View Related
Nov 14, 2003
is this the best and fastest method to connect to a database for DataGrid databind?
---------
Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand = New SqlCommand("sp_BuddiesPendingSelect1", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@UserID", intUserID))
MyConnection.Open()
Dim dr As SqlDataReader = MyCommand.ExecuteReader()
DataList1.DataSource = dr
DataList1.DataBind()
dr.Close()
MyConnection.Close()
-----------------------
or should i use a DataAdapter and fill a DataSet and then attach the DataSet to the DataGrid?
View 3 Replies
View Related
Feb 6, 2006
I have been using VB6 for a long time now and had no problems using ADODB.Recordset.I had a module to which I would send my recordset (byref) and the SQL command (byval) and use the resultant recordset for adding, modifying or deleting records.How can I do the same with VS2005? This is a major problem as there are dataadapters, grids, datareaders etc.Does someone have a simple method to get the recordset so that I can modify the record using the code (eg: .Addnew/.Delete/.Update) and Close the connection?Please note that I do not need to display anything and have to run 40/50 AddModDel for every click of the program.Any help is greately appreciated. Thanks in Advance.
View 1 Replies
View Related
Nov 11, 2004
If you not familiar with it, the <a href="http://www.stephenforte.net/owdasblog/permalink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8">Rozenshtein Method</a> uses SQL to create a crosstab. The concept is a stroke of genius but I'm having trouble getting it to work on one of production databases.
I successfully used the Northwind example explained at Stephen Forte's site (see the link above)...but no luck on my real world problems.
I can get the date statements to resolve to 0 correctly, but when I try to aggregate the data - the statements are turning into 1 multiplying the aggregate data for each cell...which fills in the same data across the entire row.
For example (the columns represent the time period),
GROUP T1 T2 T3 T4
group1 9 9 9 9
group2 3 3 3 3
group3 5 5 5 5
My sql code is:
SELECT dbo.tblHassBatch.ProdLine,
COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(dd,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS Today,
COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Week],
COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Month],
COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Month]
FROM dbo.tblHassBatch INNER JOIN
dbo.tblHassUUT ON dbo.tblHassBatch.BatchID = dbo.tblHassUUT.BatchID
GROUP BY dbo.tblHassBatch.ProdLine
Am I overlooking something here? I'm pulling my hair out b/c if this works it's really going to provide a great solution for a project I'm working on...but I can't seem to figure it out.
Any help is appreciated.
Thanks,
Alex8675
View 12 Replies
View Related
Jan 25, 2006
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
View 2 Replies
View Related
Feb 26, 2004
'TOP 1' or 'DISTINCT' or 'MAX'
Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.
View 14 Replies
View Related
Apr 17, 2008
Hello, I've a table "TblSales" with this structure: Year (integer), CustomerId (integer) and Sales (float).
SELECT Year, CustomerId, Sales FROM TblSales
Year;CustomerId;Sales
2000;1;100
2000;1;50
2000;2;200
2001;1;10
2001;2;20
etc...
I've 10 distinct CustomerId (from 1 to 10)
I want to make a row for every year with x columns (1 for every diferent CustomerId) with the sum of sales of this Customer on this year.
I've thounsdands rows. What's the more eficient method?
Thanks
View 2 Replies
View Related
Aug 3, 2007
Hi Team
Can someone please tell me how to fix this following error in SQL 2005
When trying to create a maintenance plan,
Method Not Found:'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIYtils..ctro()'
(Mircosoft.SqlServer.MaintenancePlanTasksUI)
Any assistance would help
Thanks
View 1 Replies
View Related
Aug 21, 2007
I've a question regarding SSRS 2005.
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
All suggestions are highly appreciated .
Thanks,
View 3 Replies
View Related
Sep 10, 2007
Hi Guys i get this error when trying to setup a new maintenance plan
Method not found 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'.(Microsoft.SqlServer.MaintenancePlan.TaskUI)
I have look and googled and can't seem to find the solution.
any help would be appreciated.
Running SQL 2005 SP2
View 3 Replies
View Related
Nov 24, 2006
I have never used replication before and I have been asked to considerit in a project I am currently working on.I have created an application for a sales team which is loaded on theirmachines, it uses ms sql as its data source and connects via theinternet back to the central server in the office.Problem is this has shown to be too slow causing time out errormessages and so on. I have been told to research the possibility ofreplication, but am unclear what type of replication to use or where tostart.Any assistance would be appreciated.Regards,Ben
View 4 Replies
View Related
Jul 20, 2005
Hi,Given 2 tables:Table1:id Auto,int,PrimKeytable2id inttxt nvarchar50Table2:id Auto,int,PrimKeyorder intThe scenario: Table2.order defines how the table1.txt is should be ordered.Table1.table2id contains the id of the order. This cannot be changed :(How do I select all Table1.txt values but ordered by their correspondingvalues of the table2.order field?--Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
View 7 Replies
View Related
Jun 7, 2006
Greetings SSIS friends
I want to implement the following query using SSIS Data flow Source component :
SELECT * FROM someTable WHERE someColumn = 'H'
How do I restrict the data coming from my data source? By that I mean how do I apply the WHERE clause in SSIS?! Should I use a conditional Split component?! but that would mean retrieving all records first then adding the split component (not the most efficient method surely).
Any suggestions would be much appreciated.
View 6 Replies
View Related
Sep 2, 2007
I need to add a value to a column that I know to be DT_CY. However, there is no PipelineBuffer.SetCurrency() method or anything similar.
I checked out the docs for PipelineBuffer.SetDecimal() and it states "If the DataType of the buffer column is not DT_NUMERIC, an UnsupportedBufferDataTypeException occurs"
Similarly for PipelineBuffer.SetDouble() it says "If the DataType of the buffer column is not DT_R8, an UnsupportedBufferDataTypeException occurs"
So it seems I can't use either of those two methods. How do I push a valueto an output column of DT_CY when building an async component?
By the way, I could ask the same about
DT_DBDATE
DT_DBTIME
DT_FILETIME
DT_IMAGE
DT_TEXT
DT_NTEXT
DT_R4
DT_UI1
as well because according to the documentation there doesn't seem to be a method that supports those either. So, please could you answer the same question for those as well!
Thanks
Jamie
[Microsoft follow-up]
View 13 Replies
View Related
Feb 27, 2008
Hi frnds....
I have 1 project in VC++ and 1 in Vb.net
I have added the VC++ project in VB.Net project using 'Add Existing item option'
Now i want to use a method from VC++ project in VB.net
How can i do so?????
Thanx in advance..
View 4 Replies
View Related
Jun 3, 2006
Hi,
I want to learn what is going on Example: Production Scheduling at http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp
First, please interview Production Scheduling example. I'm curioing about SendResultsEnd method.
foreach (Item item in items)
{
// Compute the schedule and report it
item.ComputeSchedule();
item.OutputSchedule(pipe);
}
pipe.SendResultsStart(record);
for (int i = 0; i < size; i++)
{
// Pipe each day of production. Omit zero production
// days.
if (schedule != 0)
{
record.SetDateTime(1, (DateTime)(dates));
record.SetInt32(2, schedule);
pipe.SendResultsRow(record);
}
}
pipe.SendResultsEnd();
Second code block is a part of OutputSchedule method. When pipe.SendResultsEnd method executed. First code block moves to next item. If SendResultsEnd method sends a resultset to client. This code will send many of resultset to client how many items array has in. If it is sending many resultsets. How can client can catch them? I was confused about this situation. And I don't have SQL Server 2005 in my computer. If you test it and explain what is going on I'll be appreciate to you. Please review example in the link.
Thanks. Happy coding.
View 1 Replies
View Related
Mar 14, 2008
Hi All,
I am developing an application in which i have to send data from local Sql Server compact edition database[Which is in a Windows Mobile Device,] to central server[SQL Server 2005]. I am using RDA method for communication
Can i use push method to send data from local DB to Central DB?
Is it must to use PULL method before using PUSH method?
Thanks in Advance..
View 8 Replies
View Related
Feb 1, 2007
I have three options in my mind to synchronize my data of sql server compact edition with sql server 2000/sql server 2005.
1)Merge Replication
2)Remote Data Access
3)Web Service
All the data would be residing on Sql server 2000/2005 and user will get their data after being authenticated and will change the data and add new data and then again will synchronize with server with new changes.
Please suggest me solution, my opinion is going towards Web service.
Thanks
View 14 Replies
View Related
Jun 29, 2006
I have a column of values in one table that needs to be updated based on an adjustment factor in another table. The table which contains the adjustment factors is built with the following column names.
RangeBeg RangeEnd Factor
The factor is determined depending on where the value falls in the range. I have been trying to do an sql script with no luck. Any Help would be appreciated.
Thanks
View 4 Replies
View Related
Nov 20, 2006
Does UrlEncode have any impact on SQL injection? How would I go about protecting my site?
View 3 Replies
View Related
Jun 13, 2007
I have spent the afternoon researching this, and to no avail so here's my question.
I have a user input form with upwards of 60 fields that are to be filled in by the user (intranet environment). I want to post those values as a new record in an SQL database. I have setup an SQLDataAdapter tied to a parameterized stored procedure on the SQL server. I can successfully post data from the controls on the form when I set the parameter type to Control based.
Here's the problem, though. I need to do some pre-processing on most of the values in the controls on the form. I have setup VB code (that executes when the user clicks the "Submit" button), that pulls in each control value, performs any necessary processing, and sets a variable to the value I actually want stored in the DB. How can I assign THAT value (from the variable) to the parameter for the stored procedure?
Thanks,
Dave
View 5 Replies
View Related
Jul 11, 2007
I'm creating an application that will allow users to contribute "content". The content can be tagged, saved as "my content", etc... very web 2.0'ish. The site will rely very heavily on SQL Server 2005. By default, there is a Content table that simply stores the content with an identity column PK. There is also a Tag table and User table.
What is the most effective schema for speed and reliable scalability? Eventually there could be hundreds to thousands of people contributing and tagging content.
Idea 1: Lookup TablesSimply make a new table that holds the TagID and UserID. The table will be very important as it will be queried very regularly to show the users which tags they have stored.Pros: This will effectively allow me to store & query what tags the user has selected. It's simple to setup and the application won't have to work much with the data returned from the query.Cons: What happens when there are thousands of users tagging content? At what point does it become very inefficient to query a table that has a huge number of rows?
Idea 2: Comma Delimited ListSimply has a field in the user table that has a comma delimited list of TagID's the user has selectedPros: Keeps table size low, fairly easy to implement.Cons: Application has to perform more work. It has to separate the TagID's by comma, and requery the database to get each tags data, based on TagID.
Those are basically the only two methods I've really got experience using. The reason for this post is to see if there are methods that I'm not aware of that are better suited for what I'm trying to do.
Any assistance is greatly appreciated, thank you in advance!
View 5 Replies
View Related
Jul 30, 2007
Dear all,
i am using asp.net ,C# (VS 2005) and sql server 2005.
i have written sp for inserting the the data which written last inserted idendity no.
i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form. As executenonquery return only affected rows.
please guide me.
thanks
View 3 Replies
View Related
Sep 20, 2007
Hello,
I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL.
Thanks.
View 3 Replies
View Related
Nov 5, 2007
Hi,
I'm wondering about the following:
I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL.
I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.
So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?
Thanks in advance for your advise.
Stewart
View 3 Replies
View Related
