|
Don't cross post in multiple forums. It's considered very rude to "spam" the forums and makes it quite difficult for multiple people to collaborate on an answer.
|
|
|
|
|
Hi,
I want to improve the following query's performance executing on sqlce.
select *
from TABLE_A
where field_a in (select field_a
from TABLE_B
where field_b = '0000162080'
and field_c = '2'
and field_d = '011')
order by field_e
Table_A has index for field_a and Table_B has index for field_b and field_c. How can I rewrite the query to execute faster?
thanks,
- ferudun -
|
|
|
|
|
I don't use SQL CE, but the principles are generally the same for different database products:
Do you have separate indexes on field_b and field_c? If you do then try using a composite index instead:
create index table_b_idx2 on table_b (field_b, field_c, field_d, field_a)
Also, you may be able to get the query to run faster by changing the "select *" bit so that it just returns the columns that you actually need to use.
|
|
|
|
|
I have a table :
ID ___ IDUser ___ Year
1 ___ 1 ___ 2005
2___ 1 ___ 2007
3 ___ 1___ 2003
4___ 2___ 2008
5 ___ 2___ 2005
I want to filt : unique IDUser with Max year :
ID ___ IDUser ___ Year
2___ 1 ___ 2007
4 ___ 2 ___ 2008
Please help me. Thanks.
|
|
|
|
|
Something like this:
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Error s: SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year
Exception : "Column IDUser is invalid in the select list because it is not contained in erther an aggregate function or the Group by clause"???
|
|
|
|
|
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY IDUser) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
It is OK. Thanks you very much !
|
|
|
|
|
Ah... Sorry - I mistyped the query. Glad you figured it out.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
select A.CategoryName, B.CustomerId , sum(A.CategoryId) as SumOfCID from Categories A , Customers B
the above query makes the following error
you tried to execute a query that doesn't include the specified expression 'CategoryName' as part of an aggregate function.
i am using vb6 and access
-----
thanks
|
|
|
|
|
use GROUP BY clause
select A.CategoryName, B.CustomerId , sum(A.CategoryId) as SumOfCID from Categories A , Customers B GROUP BY A.CategoryName, B.CustomerId
request you to pls check GROUP BY clause usage in SQL (language)
hope you are not aware
Note:
you are fetching data from two table and there is no join specified.
Regards
KP
|
|
|
|
|
how to use auto increment property in ASP.NET
|
|
|
|
|
Auto increment could be done in sql server 2000 by using identity.
You wanted in ASP.Net, could you explain in brief what exactly you are looking for?
Gautham
|
|
|
|
|
I need to connect the MySql database. Can anyone tell me what's the namespace to use for the connection and how do to connect .
Regards,
LEE
|
|
|
|
|
Create ODBC connection and create DSN for ur MySql DB.
rahul
|
|
|
|
|
Ok..! the namespace for the MySQL is Odbc: what object is used for connection
Regards,
LEE
|
|
|
|
|
Dear all,
i am using sql 2005 , asp.net and C#.
i have made reference to two table that is master and transaction.
sp that no data in transaction will be saved if its not exist in master. Now when i goes to delete the record which is there in transaction. i get refercial table error message. i would like to know how can i make sure that if data exist in transaction table. instead of sql error i pop up some error.
please guide me.
thank you
regards
imran khan
|
|
|
|
|
How can i view the size of a record in sql server express 2005? What is the query?
X
|
|
|
|
|
You can work out the average size of a record by running:
exec sp_spaceused 'MyTableName'
That gives you the number of rows, and the amount of space taken-up by the data and indexes.
|
|
|
|
|
Hai everybody,
I am new to triggers in SQL Server.
Can you please tell me can i combine the insert and update events in the SQL trigger as we do it in Oracle.
If yes, then how can i check it whether it is an inserting or updating event. (In oracle we will use INSERTING or UPDATING or DELETING.)
I am going to update a column with 1 (as it is a bit field) and then i have to perform some steps based on the value and later on again i want to reset the value to 0 in a trigger.
Can i perform this? If yes or no , how can i do it?
-- modified at 1:47 Monday 25th June, 2007
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
|
I have gone through the reference what you gave. but there is no hint for combining the insert and update events in a single trigger?
I want to combine them because i am going to perform the same operations partially.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
CREATE TRIGGER TRIGGER_NAME
ON TABLE-NAME
FOR INSERT, UPDATE
.....
.....
.....
you can achieve your functionality by writing your logic in this trigger!
Gautham
|
|
|
|
|
Hi ,
I installed the SQL Server 2005 with all the components.actually i want to bractice the SQL Server Business Intelligence Development Studio as i study.....the thing is SQLServer BI is different, meening that new -> project template is diffent..It is same as MS Visual Studio 2005.(I have already installed that also.)
i want BI as it is in initial...
something wrong that i doing?? Is it same? i want that as it is...any setting to build..
Thanks in advance!
|
|
|
|
|
While raising an error using the RAISERROR statement is there any specific state and level no to be given, will it affect any thing. If so what nos i can specify?
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)