|
Hi,
I have employee table.
ID empno salary empname
1 1 5000 sdf
2 2 4000 sdf
3 3 4000 sdfas
4 4 4500 sdf
5 5 5000 k
6 8 1000 TT
I tried to retrieve the non duplicate rows from the using.
Ex: the output should be
ID empno salary empname
1 1 5000 sdf
2 2 4000 sdf
4 4 4500 sdf
6 8 1000 TT
It should not be display the duplicate values in the salary.
Thanks
Sujatha
|
|
|
|
|
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM
tblName GROUP BY Id HAVING COUNT(Id) = 1))
--> This query gives you a non duplicate rows
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM
tblName GROUP BY Id HAVING COUNT(Id) > 1))
--> This query gives you a duplicate rows
|
|
|
|
|
Your first return all rows, second query return no rows.
Regards
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
here it is:
SELECT <br />
(SELECT TOP 1 t1.id FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS id ,<br />
(SELECT TOP 1 t1.empno FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empno, <br />
salary,<br />
(SELECT TOP 1 t1.empname FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empname<br />
FROM dbo.TableName<br />
GROUP BY salary<br />
ORDER BY id ASC
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I tried to execute the above query in the msacess. it throws an error msg as
you tried to execute a query that does not include the specified expression 'id' as part of an aggregate function
Kindly help ASAP.
|
|
|
|
|
remove line ORDER BY id ASC
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
It's working.
Thanks a lot.
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi all,
I am using SQL Server 2005.
I wrote a storedprocedure for getting data from data base.
It is compiled successfully but when i run that SP by passing params i getting error.I think my stored procedure is not correct.
My Stored Procedure:
////////////////////////////////////////
USE [ProxyAds]
GO
/****** Object: StoredProcedure [dbo].[USP_AdsReport] Script Date: 08/13/2008 10:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AdsReport](
@specialization smallint = null,@country smallint = null,@state varchar(50)= null,@city varchar(50)= null,@from datetime = null,@to datetime = null,@user varchar(50) = null) as
BEGIN
SET NOCOUNT ON;
DECLARE @query nvarchar(1000)
SET @query =N'SELECT adsCounter.int_AdID,
transAds.str_Name,transAds.str_UserID,adType.str_Description,
adSubtype.str_Description,specialization.str_Description,
adsCounter.tinyint_Duration,adsCounter.str_FocusState,
adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter
JOIN TRANS_ADS AS transAds ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code
JOIN CONFIG_COUNTRIES AS country ON adsCounter.smallint_CountryCode = country.smallint_Code JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1'
if (@specialization != null OR @specialization != 0)
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +@specialization
if(Ltrim(Rtrim(@country)) != null OR @country != 0)
SET @query = @query + ' AND adsCounter.smallint_CountryCode ='+@country
if(Ltrim(Rtrim(@state)) != '' OR @state != null)
SET @query = @query + ' AND adsCounter.str_FocusState ='''+@state+''
if(Ltrim(Rtrim(@city)) != '' OR @city != null)
SET @query = @query + ' AND transAds.str_FocusCity ='''+@city+''
if(@from is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon >='+@from
if(@to is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon <='+@to
if(Ltrim(Rtrim(@user)) != '' OR @user != null)
SET @query = @query + ' AND transAds.str_UserID'''+@user+''
print @query
Execute sp_sqlexec @query
END
/////////////////////////////////////////////
Execution in SQL Management Studio:
USE [ProxyAds]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[USP_AdsReport]
@specialization = NULL,@country = 7,@state = NULL,@city = NULL,@from = NULL,@to = NULL,@user = NULL
SELECT 'Return Value' = @return_value
GO
Error :
=======
Conversion failed when converting the nvarchar value 'SELECT adsCounter.int_AdID,transAds.str_Name, transAds.str_UserID,
adType.str_Description,adSubtype.str_Description, specialization.str_Description,adsCounter.tinyint_Duration,adsCounter.str_FocusState,adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter JOIN TRANS_ADS AS transAds
ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code JOIN CONFIG_COUNTRIES AS country
ON adsCounter.smallint_CountryCode = country.smallint_Code
JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype
ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1 AND adsCounter.smallint_CountryCode =' to data type smallint.
.............Please help me where i went wrong in SP
Thanks in advance.
|
|
|
|
|
You have to defined @specialization and @country as varchar.
|
|
|
|
|
Thanks, but where & How.
I am passing smallint values as params to SP.
Cheers,
sekhar
|
|
|
|
|
You can do it like this:
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +cast(@specialization as varchar(1))
|
|
|
|
|
Thanks once again.
It is working for smallint params,
i did similar to as u told.
SET @query = @query +' AND adsCounter.smallint_Specialization = '+Convert(nvarchar,@specialization).
It is working fine. But What i have to do for DateTime parameter.
When i am passing DateTime param @from as '8/4/2008'
I got an error saying :Conversion failed when converting datetime from character string.
What i have to change for DateTime ?
|
|
|
|
|
convert(varchar(10), @from ,120)
Here '120' in behalf of the format type
modified on Wednesday, August 13, 2008 3:49 AM
|
|
|
|
|
Thanks a lot. Its working fine.
|
|
|
|
|
Hi All,
Ok I know theres a command(sproc) to check for deadlocks and to remove them. Can someone refresh my memory plz?
DNM
|
|
|
|
|
Don't know about removing deadlocks, but sp_who2 will show who is being blocked and by whom.
sp_lock will show locked objects
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Hi,
Don't remember any sp to detect deadlocks or remove them since this is an automatic process in SQL Server (except in Compact edition). However you can:
- configure the amount of time for deadlock detection
- set deadlock priority for a session
- use trace flags to obtain more data on deadlock
Mika
|
|
|
|
|
HI,
i really donot know how to remove deadlock but yes we can configure der time n other factors.
sp_who2 and sp_lock will give u list of object which are blocked.
|
|
|
|
|
IS it proper to make tables where you would have an object in c# or vb? Like as an example I am storing in this case blood pressure data. which arm, what time, if they were resting, the actual blood pressure, etc. But for each patient they get their blood pressure taken 4 times so I really need to store 4 of each item. So my question is, is it correct to make a table called blood pressures and store 4 entries for each patient?
|
|
|
|
|
bfis108137 wrote: So my question is, is it correct to make a table called blood pressures and store 4 entries for each patient?
Depends on how you like it. Actually this is not bad.
But still, we can break this into two tables; lets say PatientDetails(with all crap like his name, address but most important an ID which will be used to map the other table) and PatientBloodPressure.
Hope this helps
|
|
|
|
|
Is it ok to put it all in one because I will tell you that I think it might make my databindings easier. I do want to do it professionally though because they are paying me after all and I don't want someone to come by later and get really crazy mad at the programmer that wrote it.
|
|
|
|
|
bfis108137 wrote: I do want to do it professionally though because they are paying me after all
Appreciable
See, why I suggested having two tables was due to the fact that in future they may want have report or functionality which only wants Patient Details. Now this thing has nothing to do with his Blood Pressure. Or in some other case they may want to store some other test details which should be seperate from the Blood Pressure.
Did I make my point clear?
|
|
|
|
|
bfis108137 wrote: make my databindings easier
You can use a JOIN when you query the data.
|
|
|
|