DISTINCT Conflicting With My Query

May 10, 2006

I need p.phonenumber in there but then it messes up my DISTINCT ON m.number because I end up with dup m.numbers if I run just the inner select statement during testing.

But then I need the phonenumber in that subquery so that I can do list.phonenumber

ahh!



UPDATE Master
     SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
     (SELECT DISTINCT p.number, topphone.phonenumber
        FROM phones p
        INNER JOIN (SELECT top 1 phonenumber from phones) as topphone ON topphone.number = p.number
        WHERE    p.phonetypeID = 1
               AND ISNULL(p.good, 0) <> 0
               AND LEN(p.phonenumber) = 10
     ) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''

Error:

Msg 156, Level 15, State 1, Procedure Skiaa, Line 66
Incorrect syntax near the keyword 'top'.
Msg 156, Level 15, State 1, Procedure Skiaa, Line 88
Incorrect syntax near the keyword 'top'.

I want to update m.homephone with the p.phonenumber based in the filtering critiria I have specified but this simple task is turning out to be a pain.

View 1 Replies


ADVERTISEMENT

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

View 2 Replies View Related

MS SQL Conflicting With Other DB Engines.

May 18, 2004

Hello All,
I work for a company that packages MSDE and MSSQL 8.0 because our software requires storing client information in a database. The problem is that we've found that if the our users have another DB engine installed (BDE or Paradox usually), our program has trouble using MSDE to access the database and thus making our software inaccessible. It behaves as if this other database engine locks on our database before MSDE can. I'm basically looking for help on either how to uninstall these other db engines or how to get around this problem so that both MSSQL and other db engines can co-exist in one system.

View 12 Replies View Related

Conflicting Databases

Jan 16, 2008

I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"

But When I go through dos and type osql -S(local)database -E
it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????

View 6 Replies View Related

Conflicting Sql Versions

Jan 17, 2008

I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"

But When I go through dos and type osql -S(local)database -E
it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????

View 3 Replies View Related

How To Solve Conflicting Component ID's

Feb 2, 2007

Hello,

I have 3 packages in one solution:

package A is the main package and calls packages B and C using 2 "Execute Package Task"s.
package B is a package constructed from scratch.
package C is a package started by file copying the package B .dtsx file in Windows Explorer and then adding/removing/renaming content.

If I run packages B and C separately they work fine.

If I run package A, package B is started and finishes correctly. Then package C is started and stops somewhere in the middle.

I guess this is because some of the component id's within package C are identical to those in package B ( I checked in the dtsx packages using Notepad. ) and somehow SSIS remembers the precedence constraints of package B and stops executing the rest of C. But I am not sure. And I do not know how to solve this without a risk of losing two days of work.

Any advice/suggestions ?

TIA

Jan Vandepitte

View 6 Replies View Related

Log Shipping Conflicting For Setting DR Server

Dec 12, 2007

Hi all,

I need some advice on this scenario.

I have to set a Disaster Recovery (DR) server from my production Server( Most probably, i will be using Database Mirroring for my DR Server). Right now, the production is log shipped to another server for reporting every 15 minutes.

So, for setting up Database Mirroring onto my DR Server, i can take Full Backup from production without damaging log Shipping.
But, for setting up Database Mirroring, we need to restore most recent Transaction log backup with norecovery ( Correct me if i am wrong).

But, I cannot take log back because the prodcution database is log shipped for Reporting needs. If taken, log chain breaks.

Can anyone please suggest some work around for setting up the DR Server with minimal downtime.

Thanks for all your help.

View 5 Replies View Related

Inserting Nulls In To Table Conflicting With Rule

Jan 15, 2007

Hi,I basically have two tables with the same structure. One is an archiveof the other (backup). I want to essentially insert the data in to theother.I use:INSERT INTO table ( column, column .... )SELECT * FROM table2Now, table2 has a rule on various columns:@CHARACTER IN ('Y','N')but the column allows nulls, in the design view is says so anyway.When I run this query I get:A column insert or update conflicts with a rule imposed by a previousCREATE RULE statement. The statement was terminated. The conflictoccurred in database 'database', table 'table', column 'column'.The statement has been terminated.Obviously, I've changed the names of everything.The only data in those columns which could possibly conflict with therule is the NULL value. Any ideas why this doesn't work?Thanks.

View 8 Replies View Related

Join Filter Causes Conflicting Locking Hints

Jul 14, 2006

Hi,

Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).

Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:



Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1



fn_GetIDList is the function used in the original filter.

Thanks for any help



Graham

View 6 Replies View Related

Transact SQL :: Reducing Deadlocks From Conflicting Delete Statements

Jun 15, 2015

I am working on an app that getting quite a few deadlocks due to delete statements.  I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks.  I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.

What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.  

Open this url in new tab [URL] ....

View 7 Replies View Related

DISTINCT QUERY PLEASE HELP

Oct 23, 2005

I do an distinct query like this:query = "SELECT DISTINCT name, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"The query works fine but I need to select one more field called "info" so I just tried this:query = "SELECT DISTINCT name, info, total  = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"but then I get the error message:"Column 'products.info' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"I want do group just by "name" and not "info"How can I select this "info" field to?? somebody know??

View 2 Replies View Related

TOP Query With DISTINCT

May 22, 2000

Have a query that uses TOP 10; but I'm not receiving unique results and haven't been able to figure out using DISTINCT with TOP. Is it possible to use TOP with DISTINCT?

Thanks for any help/suggestions.

View 1 Replies View Related

Distinct Query Help

Sep 10, 2007

i'm in need of help woth this query, i've edited my post.

just click the link below

http://www.geocities.com/gateofgalesph/qry/index.html

thank you :)

View 1 Replies View Related

Distinct Row Query

Jun 18, 1999

I need help with a query on a single table.

The table in name, date, ss# and I need to get unique rows, but there can me many ss# to each date. I need to get the max date for each unique ss#.

Select distinct name, date, ss#

returns all rows, because dates are different, but I need to have the results contain all three columns of information.

Thanks for the help,

-Ken

View 1 Replies View Related

Distinct Query Help

Jan 8, 2005

Hi,

how can i run a distinct query on ONE column from one table that will show all columns.

SELECT DISTINCT *
FROM invInactive
WHERE (status = '13')

tagnum, status, inactivedate
----------------------------
1033, 13, today
1033, 13, yesterday
1095, 13, today
1205, 13, yesterday

basically, i just want to show this:
tagnum, status, inactivedate
----------------------------
1033, 13, today
1033, 13, yesterday

thanks for your help.
gonad.

View 1 Replies View Related

Distinct To Query

Jun 12, 2005

Code:

CREATE PROCEDURE [dbo].[CastCompare]
@ID int,
@TopRecords int,
@Type int

AS

DECLARE @SQLSelect nVarchar(1000)

SET @SQLSelect = 'SELECT TOP ' + CAST(@TopRecords AS Varchar(10)) + ' * FROM TblCastList,TblCast where TblCastList.ProductResellerID=TblCast.ResellerID and TblCastList.ProductID=' + CAST(@ID AS Varchar(10)) + ' and TblCastList.Type=' + CAST(@Type AS Varchar(10)) + ' order by TblCast.[name] asc '

Exec sp_executesql @SQLSelect
GO



how Can I add distinct command to query ?
I tried something but none of them is work.

I want to add
distinct TblCastList.[ProductResellerID]

View 3 Replies View Related

A Distinct Query

Aug 2, 2004

I have 2 tables as following :

tbl_Articles
3ArticleIDint
0AuthorIDint
0ArticleTitle
0ArticleText
0ArticleDate

tbl_Authors
3AuthorID
0AuthorFullName
0AuthorEmail
0AuthorDescription
0AuthorImage

I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

If anyone knows the solution i will be glad .
Thanks from now on

View 2 Replies View Related

Distinct Query

Nov 7, 2005

When i run this query,
select distinct(firstname + ' ' + lastname), firstname, lastname, department
from tbltest

it brings out 149 records

but when i run it with the extension field

select distinct(firstname + ' ' + lastname), firstname, lastname, department, extension
from tbltest

Is there like a limit to the distinct query on the number of columns returned ?

View 1 Replies View Related

Query Help, Distinct Row

Apr 21, 2008

I have the following query:

SELECT DISTINCT PC.ContentID, PC.GeoUnitID, PC.ContactID, CT.ID, CT.PageTitle, C.FirstName, C.LastName, C.MainPhoneNO, C.EmailAddress
FROM ProductContacts PC INNER JOIN Content CT ON PC.ContentID = CT.ID INNER JOIN Contacts C ON PC.ContactID = C.ContactID
WHERE PC.GeoUnitID = @GeoUnitID
ORDER BY CT.PageTitle ASC

The problem I'm having is two rows have the same ContentID with a different ContactID. I want to only return a single row for each ContentID.

Is there a way around this?

View 3 Replies View Related

Help With Distinct Query

Aug 15, 2007

Here's my query:

SELECT NotYetPublished, Available, Catalogx, pubdate, pubdate_full, NYP, Fulfillment, Title from finaldata where available <> 'Out-of-Print' or (totalnew <>0 and totalused <>0 and totalcollectible<>0) order by NYP desc, pubdate_full desc, Fulfillment desc, Title

I only want to return records that do not have the same Catalogx. I can't use distinct since some of the other data is different.

How can I accomplish this? Thanks in advance.

View 4 Replies View Related

Help With A Distinct Query

Nov 12, 2007

Hello everybody

I'm kind of newbie and I like to know if you can solve me a question,

The fact is I'm trying to gather some information and I don't know how. What I want to do is retrieve the values of five columns, the first one is the primary key, but the others must be unique (all together)

PK Value1 Value2 Value3 Value4
1 3 5 6 3
2 3 5 7 2
3 4 3 1 4
4 3 5 6 3 <- =PK1


I've tried with:

SELECT MesuraA, MesuraB, MesuraC, MesuraD
FROM (SELECT DISTINCT MesuraA, MesuraB, MesuraC, MesuraD
FROM barres_Ciampalini) AS derivedtbl_1

But I don't know how to retrieve the Id.

How Can I do that?

Thanks for read

View 8 Replies View Related

DISTINCT QUERY

Jul 20, 2005

This is probably easy but I can't work it out. I have this statementSELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.NameFROM dbo.CIF_Departures INNER JOINdbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint= dbo.CIF_PlaceReference.PlaceIDORDER BY dbo.CIF_PlaceReference.NameThis results in a column of placenames which is OK. There are also multiple'time of day' values against each placename however I only want to returnthe one nearest to the current time. If I do this...SELECT DISTINCT TOP 100 PERCENTdbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTimeFROM dbo.CIF_Departures INNER JOINdbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint= dbo.CIF_PlaceReference.PlaceIDORDER BY dbo.CIF_PlaceReference.Name.... I get multiple place names.Any ideas?

View 8 Replies View Related

Getting Distinct Entries In Query.

Mar 18, 2008

Hello,
I have a reference table.  This table has an effective date and end date, and I need to get a single set of values at a point in time.  Suppose the following hotel rating values are the values that I want to pull out of the table:
1  Star2 Star
3 Star
4 Star
5 Star
This value has other information associated with it (benchmarks and such) and so it's possible that one of the 2 star entries gets end-dated, and then a new 2 Star entry gets created with an effective date and no end date.
How can I query so that I only get one 2 Star entry at any point, and get the point in time where the date is between the effective and end date, but only pull back a single set of entries, one distinct rating value.  So, suppose the first entry is end-dated 7/1, if I look at old data, I want to see the old benchmarks and so I want the previous entry end-dated 7/1; otherwise, I want to see the new end-date, with the new 2 star entry with no end date.
How do I do that?  Make sense?

View 1 Replies View Related

How To Distinct Data By Using MS SQL Query

Apr 29, 2008

Select A.SNO , A.Name , B.Picfrom Student A Left Join Picture BWhere A.SNo = B.PNoNo     SNo            Name                Pic1      000            Andy                /Doc/andy.jpg2      001            Andy                /Doc/andy2.jpg3      002            VIVI                /Doc/vivi.jpg4      003            VIVI                /Doc/Vivi2.jpg The same person will only show 1 pic Pathlike this resultNo     SNo            Name                Pic1      000            Andy                /Doc/andy.jpg3      002            VIVI                /Doc/vivi.jpgcan you please help? I trid use Group By but it not works..thank you

View 10 Replies View Related

Simple Distinct Query (?)

Aug 19, 2005

I have what I thought should be a simple query that's not quite as simple as I thought. Maybe I haven't had enough coffee yet.

Here's the scenario,

Data table looks like this :


COLUMN A is Primary Key
COLUMN B is UserID
COLUMN C is (for example purposes) an int field

A B C
1 1 SOME VALUE
2 1 SOME VALUE
3 1 SOME VALUE
4 2 SOME VALUE
5 2 SOME VALUE
6 2 SOME VALUE
7 3 SOME VALUE
8 3 SOME VALUE
9 3 SOME VALUE


The query that I'm looking for would return all 3 columns but only the first row for each distinct (unique) UserID (Column B)

Hope that all makes sense! ;)

View 4 Replies View Related

Distinct Count Query

Dec 11, 2007

I have a table with following fields
tdate
custcode
prodcode


table is filled with full year data and i want following result

I want count of distinct custcode in every past three months.

for example
Result like this

month tjan tfeb tmar tapr tmay ..... tdec

prod1
prod2
.
.
prod5

And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required.
Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.

Can any1 help me please.

I am using MS SQL 2005 and above table is a big table (approx 10 million records)

Sham

View 13 Replies View Related

Query For Distinct Dates

Sep 4, 2014

I have a need to only find distinct dates in which a worker worked in the factory using TSQL

Code:
RowNumber workerstartDate workerenddate
1 2012-08-08 2012-10-10
2 2012-08-10 2012-08-31
3 2012-09-05 2012-09-15
4 2012-10-15 2012-12-19
5 2013-01-02 2013-03-14
62013-03-15 2013-05-23

Basically, I am looking for the above to look like this

Code:
rownumberworkerstartDate workerenddate
12012-08-08 2012-10-10
42012-10-15 2012-12-19
52013-01-02 2013-03-14
62013-03-15 2013-05-23

View 2 Replies View Related

Selecting A Distinct Row Query

May 13, 2004

I am trying to append data from a source table to a destination table, the only difference between the tables is that a primary key has been established on the destination table. Problem is, the source table has a handful of duplicate values in the column that corresponds to primary on destination table; so when I run the query I get a primary key conflict. What I'd like to do is select distinct values from the source table to avoid the conflict, but am having trouble getting it to run. Here is the statement-

INSERT INTO cust_master
SELECT DISTINCT cust_master_temp.*
FROM cust_mast_temp LEFT OUTER JOIN
cust_master ON cust_master.temp.CUST_NUMBER = cust_master.CUST_NUMBER

Doesn't seem to be understanding "Distinct"

Any Ideas?

View 2 Replies View Related

Distinct Custnumbers In Query

May 19, 2008

Here's my query:

Select portfolio, custnumber from table

some custnumbers are the same. However I only want the query to include one portfolio per custnumber, ideally the first portfolio.

How would I accomplish this?

Many thanks!

View 7 Replies View Related

SQL DISTINCT UNIQUE QUERY

May 4, 2007

I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int)
AS SELECT DISTINCT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID
WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepID
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

*********
With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressStepType.ProgressStepType the query should only read it once. In other words the dbo.ProgressStepType.ProgressStepType is a unique value and the rest not...

Please help, my boss is on my case :)

View 14 Replies View Related

Query Issue While Using Distinct And Sum

May 28, 2007

I am developing a web site for my buddies and I to track the winnings fom my home poker games.

Table that keeps track of games goes like:

PlayerID
PokerGameID
curWon

I want to sort by top money maker nd show total won to date

I have tried to use:

Select Distinct Sum(G.curWon) AS curCash, P.playerID FROM GameReults As G, Players As G WHERE G.PlayerID=G.PlayerID Group By G.curWon, G.PlayerID

I can not getit to sort from highet to lowest and it itrates through each record. I only want it to show distinct players and their winnings. Can you please help?

View 3 Replies View Related

Distinct Query Quandary

Oct 10, 2007

Here's my query:

Select top 500 email, custnumber from MailingList order by email

There may be some duplicate email addresses with different custnumbers. What would the query look like to only show records with unique email addresses, but still return the unique custnumbers in the query.

Thanks in advance.

View 9 Replies View Related

Simple Distinct Query

Jul 20, 2005

I'm trying to query the pubs database for last title for each author.The query returns each title for each author. How can I operate on maxof title?Here's what I'm trying:select distinct au_id,max(title_id)as title from titleauthorgroup by au_id,title_idorder by au_iddito*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved