Database

Retrieve duplicate and non duplicate query

13-Aug-08 1:19

here it is:

SELECT DISTINCT table1.[USER],table1.inv,table2.fromcountry
FROM table1,table2
WHERE table1.inv=table2.cid 
ORDER BY table1.inv

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

modified on Wednesday, August 13, 2008 8:37 AM

sujtha12-Aug-08 20:35

sujtha

12-Aug-08 20:35

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

soni uma12-Aug-08 20:51

soni uma

12-Aug-08 20:51

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

Blue_Boy12-Aug-08 21:23

12-Aug-08 21:23

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

Blue_Boy12-Aug-08 21:22

12-Aug-08 21:22

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

sujtha12-Aug-08 23:12

sujtha

12-Aug-08 23:12

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.

Blue_Boy13-Aug-08 2:01

13-Aug-08 2:01

remove line ORDER BY id ASC

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

sujtha13-Aug-08 2:15

sujtha

13-Aug-08 2:15

It's working.
Thanks a lot.

Blue_Boy13-Aug-08 2:18

Error while executing the Stored Procedure

13-Aug-08 2:18

No problem.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

K V Sekhar12-Aug-08 20:04

12-Aug-08 20:04

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.

SleepingHades12-Aug-08 20:40

SleepingHades

12-Aug-08 20:40

You have to defined @specialization and @country as varchar.

K V Sekhar12-Aug-08 20:45

12-Aug-08 20:45

Thanks, but where & How.
I am passing smallint values as params to SP.

Cheers,
sekhar

SleepingHades12-Aug-08 21:03

SleepingHades

12-Aug-08 21:03

You can do it like this:
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +cast(@specialization as varchar(1))

K V Sekhar12-Aug-08 21:30

Re: Error while executing the Stored Procedure [modified]

12-Aug-08 21:30

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 ?

SleepingHades12-Aug-08 21:39

SleepingHades

12-Aug-08 21:39

convert(varchar(10), @from ,120)
Here '120' in behalf of the format type

modified on Wednesday, August 13, 2008 3:49 AM

K V Sekhar12-Aug-08 21:57