|
Tim's answer is correct but in certain cases it's more efficient to use following syntax (and in other cases this is unefficient)
select * from (select top 3 * from tablename order by columname) aliasforquery1
except
select * from (select top 2 * from tablename order by columname) aliasforquery2
Mika
|
|
|
|
|
i got something new thanx
|
|
|
|
|
Dear All,
I have a question about joining 2 table.
e.g. table1 contains
user---inv---amount---data2
a --- 001--- 1000 --- dfsdf
st --- 001--- 1200 --- sdfs
b --- 002--- 1300 --- sdf
c --- 004--- 1400 --- sdf
e.g. table2 contains
cid--- data--- FromCountry
001--- acd--- USA
001--- efg --- USA
001--- okk --- USA
002--- cce --- CHI
003--- eer --- UK
003--- err --- UK
004--- edr --- VOC
I would like to construct a table base on table1, and include the field "FromCountry" from table2.
I use
select table1.user, table1.inv, table2.FromCountry
From table1, table2,
Where table1.inv = table2.cid
when joining 2 table, I found there are duplicated records generated...
How can I just get the "FromCountry" from any one of "cid" record in table2? The perfer result on new table is:
user --- inv --- FromCountry
a --- 001 --- USA
st --- 001 --- USA
b --- 002 --- CHI
c --- 004 --- VOC
PLEASE HELP
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
