|
I believe you need to join the table to a subset of itself.
You create a subquery that gives you the values of project and the highest col1 and attribute that to a temptable. You then join you orignal table to your temp table on the values of col1. This should do the trick!
SELECT PT.* FROM out_pumptable PT join
(select project, max(col1) as MaxVal from out_pumptable group by project) TEMPTABLE1 on TEMPTABLE1.MaxVal = PT.col1
|
|
|
|
|
hi all.
i have a stored procedure that get 2 parameter.
i want to select a special filed from my store procedure.
can i call my procedure from a function and how i should do it.
(i need some code).
sepel
|
|
|
|
|
I got a "Database Timeout" error.
I m using an MFC application with MSDE database.
What can be the reason(s) for this??
|
|
|
|
|
An operation couldn't be completed in the database in the timescale expected. Simplistically you can think of there being two potential timeouts - one when you can't connect to the database in a reasonable amount of time (known as a connection timeout) and the other when you can't complete a database operation in a reasonable amount of time (known as a command timeout).
|
|
|
|
|
But why should a database transfer take so long when transferring on a local database????
I mean what are the possibilities????
|
|
|
|
|
There are all sorts of possibilities, from slow rebuilding of indexes (every time you do an insert/update/delete) indexes are reevaluated to single action updates that should be performed as set updates.
You need to use the SQL Profiler to see what is happening on this database.
|
|
|
|
|
Pete,
there is another problem that i face quite often.
i transfer like 20-30 rows to the database it happens fast.
But as the database size increases(1500 rows or so), insertion of 20 rows takes 20 seconds!!!!!
my program is like :
for(i=0;i<=row_count;i++)<br />
{<br />
m_pSet->AddNew();<br />
m_pSet->Update();<br />
m_pSet->Requery();<br />
}
|
|
|
|
|
yashveer wrote: m_pSet->Requery();
Is this statement absolutely necessary?
"Normal is getting dressed in clothes that you buy for work and driving through traffic in a car that you are still paying for, in order to get to the job you need to pay for the clothes and the car and the house you leave vacant all day so you can afford to live in it." - Ellen Goodman
"To have a respect for ourselves guides our morals; to have deference for others governs our manners." - Laurence Sterne
|
|
|
|
|
I dont have much of an idea myself. I used 'SAMS Teach yourself VC++ in 21 days' where they had given a database example(that's how i got started 1 year back).
But they did not use a 'for loop'. they were just adding one row on one click of a button.
Now even i wonder if 'Requery()' is necessary.
What if i put it outside the 'for loop' so that it executes only once?
From what it looks like in MSDN Requery() is used to Refresh the Recordset
but Update() seems to be absolutely necessary after every row is added.
|
|
|
|
|
yashveer wrote: What if i put it outside the 'for loop' so that it executes only once?
Much more sensible.
yashveer wrote: ...Update() seems to be absolutely necessary after every row is added.
True, because that's what actually commits the changes to the databse.
"Normal is getting dressed in clothes that you buy for work and driving through traffic in a car that you are still paying for, in order to get to the job you need to pay for the clothes and the car and the house you leave vacant all day so you can afford to live in it." - Ellen Goodman
"To have a respect for ourselves guides our morals; to have deference for others governs our manners." - Laurence Sterne
|
|
|
|
|
hi there...
I'm writing a query to check whether a user is already existing in the database so i can avoid re registering him.
i was trying to take the count of users having the same details. if its zero the user doesn't exist if one then he already registered.thats were i ran into problem. One of my fields in the form is a dropdown list. ie if the user gives the same data for the all other fields and choose a different option in the dropdown list the user can be registered as a different user. i want to avoid this. how to do?????
This is my query
create procedure sp_chkUnique
{
@rrf_id bigint,
@cand_name varchar(100),
@cand_fathername varchar(100),
@cand_dob varchar(50),
@gender varchar(50),
@cand_email varchar(200),
@cand_verification varchar(50),
@pincode varchar(10)
}
as
select count(rrf_id)
from cm_rm_tb_042
where rrf_id=@rrf_id and
cand_name=@cand_name and
cand_fathername=@cand_fathername and
cand_dob=@cand_dob and
gender=@gender and
cand_email=@cand_email and
pincode=@pincode;
its the @cand_verification field thats coming from a dropdown list.i cant jst avoid checking it either...
plz help me 
|
|
|
|
|
You aren't using @cand_verification anywhere in the select, so you should be OK. Basically, if all of the other information is present then @count will return none-zero. The rule here is that you can prevent duplicates by checking those fields that you consider will make it a duplicated record, and exclude those fields that would make it unique.
|
|
|
|
|
Thanks for replying...
i didn't use the @cand_verification coz i dnt knw how to use it to check the existence of the user. 
i cant jst leave that field out. i need to check that condition too.
plz help 
|
|
|
|
|
HI all,
I have a windows application which runs a process,
I am updating database column "Status" with Processing when the application is running, and on completion I update it with Staus="Completed" or in case I close the application
I update db with Status="Interupted" .
I have problem that in case while proces is running, power supply or system turns off, the db Status="Processing", but in actual it is interupted.
How will i update?
Please help.
|
|
|
|
|
well I think there's really no way u can cope for power failure in such a case. i mean if there is no electricity, how is the Db supposed to get the status changed to 'interrupted'.
you can have two status values though, one 'incomplete' (by default) and the other 'completed'
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Gokul Harsh wrote: HI all,
I have a windows application which runs a process,
I am updating database column "Status" with Processing when the application is running, and on completion I update it with Staus="Completed" or in case I close the application
I update db with Status="Interupted" .
I have problem that in case while proces is running, power supply or system turns off, the db Status="Processing", but in actual it is interupted.
How will i update?
If I were you, I'd use a transaction and two updates to do this. I would update the record to Interrupted and then create a transaction in which I updated the same record to Processing. Then, if the server crashes, once the server restarts the database transaction manager will rollback the uncommitted transactions which would revert the record back to the Interrupted status.
|
|
|
|
|
oh yes that's good
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Thanks. I liked it. 
|
|
|
|
|
hi
i had created as SP to delete the record using where condtn.if the record doesnt exists the SP will throw an error as "No such record found" and return 0 .
But my c# code gets -1 rows affected from the sql.its ok.
My problm is how to get the "No such record found" from sql to c#.
ALTER PROCEDURE SP_deleteUserMaster<br />
@LoginId nvarchar(30)<br />
AS<br />
begin<br />
set nocount on<br />
declare @ncnt int<br />
select @ncnt=(select count(*)from UserMaster where LoginId=@LoginId)<br />
if (@ncnt>0)<br />
<br />
begin<br />
DELETE FROM UserMaster WHERE LoginId=@LoginId<br />
end<br />
else<br />
raiserror('No record found',10,1)<br />
end<br />
RETURN<br />
senthil
|
|
|
|
|
Hello!
Put this line after raiserror sentense
select 'No record found'
And also into your code
string result = (string)CommandName.ExecuteScalar();
I am not sure but may be this will help you.
Regards..
Keep Smiling 
|
|
|
|
|
thank u shah
i ll check and reply
rgds
kssk
|
|
|
|
|
Hi,
I have a problem in writing a query in SQLSERVER 2000. I have 2 tables tblemployees and tblbank. Fields in the tables
tblemployees tblbank
empid empname paymode empbankid bankid bankname
empbankid in tblemployees is a foreign key of bankid in tblbank.
some employees will have paymode as cheque or cash. I that the bankid field in tblemployees will be null.
Now I want a result set as below
empid empname paymode bankname
for this i have written a query like below
select tblemployees.empid,tblemployees.empname,tblemployees.paymode,tblbank.bankname from tblemployees,tblbank where tblemployees.empbankid=tblbank.bankid
The Actual problem comes now. This query is not retrieving employees who have bankid as null. For employees who do not have bank id the bank name should be displayed as null and the remaining fields should be displayed as it is.
Please help me with a solution for this problem
|
|
|
|
|
Perform a LEFT JOIN betweeb tblemployees and tblbank. Rows on the left-hand side of the join will be preserved, so all employees will be listed regardless of whether they have bank details.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi all
i need to retrieve values from two tables which is common....
EX:-
Table1 Table2
Itemno ItemName SNo ItemName
1 pen 1 Pencil
2 paper 2 pen
here i should get the result as
pen
paper
pencil
how to write a query for this?...
can anybody help me?
|
|
|
|
|
Hello Sandhya!
Try this.
select ItemName from Table1 <br />
union all<br />
select ItemName from Table2
Keep Smiling
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
