Mutually Exclusive Counts On Ordered Queries

Jul 23, 2005

Ive been playing with this for a few days and thought I might thow it
out for seggestions.

I have Several Queries that need counts returned

The Queries are Mutually Exclusive meaning whatever Query they return
in first they cannot be included in the counts of any queries below
them.

This set of queries for example


Select ID From Customers where FIRST_NAME = 'Chris' (would return say
150)

Select ID From Customers where ST='OH' (This would retunr say 50, BUT
Run alone it might return 70, however 20 of those were in the first
Query so they arent to be retunred in this result set.

The total for Bot Queries would be 200

But If I reverse it like so
Select ID From Customers where ST='OH' (This now returns 70)
Select ID From Customers where FIRST_NAME = 'Chris' (This now returns
130)

The total of course for BOT Queries is 200 but I dont need that total I
need the total for EACH Query depending on its ordering

What I need are the single counts depending on the order in which the
queries are run

It seems like a recursion problem, but It might go past 32 level so I
cant use recursive SQL ( I dont think )

I've thought of (or tried to think how to use Not In, Not Exist, etc
but still dosent come up with the results....)
How Can I grab the counts for each Query ?

Chris

View 5 Replies


ADVERTISEMENT

What Happens If Affinity Mask And Affinity I/O Mask Are Not Mutually Exclusive?

Jan 21, 2008



I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.

Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.

I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.

So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?

View 7 Replies View Related

Total Of Your Counts In Select Queries

Mar 19, 2007

I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:

ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25

What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance

-Lance

View 6 Replies View Related

Any Possibility Of Creating A MUTUALLY Dependent CHECK

Jul 20, 2005

Hi,DDL:-- create table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique, taskCompleteDate dateTime, taskComplete bitdefault(0));/*Business Rules:a) if taskCompleteDate is NULL (like default) then, taskComplete maynot be set to 1 (attempt to update it to 1 would fail);b) else automatically set taskComplete = 1*/I was thinking using CHECK constraint (mutual constraint if possible),along the following line:CHECK (if taskCompleteDate is null set taskComplete=0 else settaskComplete=1)Hmm, your thought?Thanks.

View 5 Replies View Related

Getting Items That Were Ordered Alone

Jun 25, 2007

Hello Experts. You may have more luck at this than me.



I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.



Example of how data looks like

I am looking for transactions like orderid 3 and 5.







OrderID
Product

1
hotdog

1
burger

1
taco

2
burrito

2
snack

2
chips

3
burger

4
hotdog

4
burger

4
taco

5
burrito

6
snack

6
chips



When i run



SELECT product, count(orderid)

From Table

Where BusinessDateID = 20060725

group by product

having (count(orderid)=1)



I only get back items that were only sold once.



I am looking for a result that looks like this









Product
Ordered alone

hotdog
2

burger
3

taco
4

burrito
32

snack
12

chips
76

View 7 Replies View Related

BCP Export With Ordered Data

Sep 13, 2000

I need to export data in a specific order (e.g. the order in which the data was inserted into the table). My BCP was doing this then just started exporting in random order. I know that SQL 7 doesn't guarantee return order unless specified by an ORDER BY clause. However, that's not possible with BCP unless you use the command line BCP with the QUERYOUT option. That's my problem. I'm using the BulkCopy object in DMO and I don't see an option to order the data or use a query with an order by statement. Any suggestions?


--Buddy

View 1 Replies View Related

Returning Ordered Result Set, Except First Row

Jan 31, 2008

Hi all,

I feel like I'm missing something really simple here...

I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")

I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.

Here's the code I'm using:

CREATE PROCEDURE GetCountries AS

SELECT Country_Name
FROM Countries
WHERE Country_Name = 'USA'

UNION

SELECT Country_Name
FROM Countries
WHERE Country_Name <> 'USA'

GO

I also tried selecting into a temp table and doing a UNION that way, but got the same results.

View 3 Replies View Related

How To Use BETWEEN With Custom-ordered Values

Jul 23, 2005

We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?

View 3 Replies View Related

Ordered Update Problems

Jan 18, 2008

I€™m trying to follow examples from: http://www.sqlmag.com/Articles/ArticleID/49240/49240.html?Ad=1 for an update statement that needs to run in a specified order.


Here€™s my problem:

I have a table:

TopicActivity
PK €“ DayNum int (this is a date in YYYYDDD format)
PK €“ TopicId int
Visits int
LifetimeVisits int

LifetimeVisits is a new column that I want to calculate the value using the previous Visits column data.

Sample Data:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 0
2008002 2 1 0
2008002 1 3 0
2008003 1 10 0

I want the end result to look like this:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 5
2008002 2 1 1
2008002 1 3 8
2008003 1 10 18

Here is the query I€™ve been trying:

WITH ordered AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY TopicId ORDER BY DayNum) AS RowNum
FROM TopicActivity
)
update ordered set
ordered.LifetimeVisits = ordered.Visits +
isnull((select o2.LifetimeVisits
from ordered as o2
where o2.TopicId = ordered.TopicId
and o2.RowNum = (ordered.RowNum - 1)),0)


The update doesn€™t seem to be happening in order as LifetimeVisits seems to have random results every time I run the query. (Usually the first couple of rows are right. I€™m dealing with thousands of records) I can€™t use a cursor as the query takes way to long.

I'm running SQL Server 2005.

View 6 Replies View Related

How Can It Automatically Generating A Ordered Number

Jul 5, 2006

hi,
i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.
as asp.net is new for me, i would have some simple problems to ask.
1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me
2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.
thanks
Rgds, universe

View 1 Replies View Related

Aggregate And Group By - Sum Of Items Ordered In One Row

Jan 16, 2014

Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:

Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
from orders o, customers c, Employees e, Shippers s, [Order Details] od
where o.CustomerID = c.CustomerID

[Code] ....

Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?

View 6 Replies View Related

Is Bookmark In A Nonclustered Index Ordered?

Jul 23, 2005

When a nonunique nonclustered index is built on top of a clusteredindex, is it guaranteed that the bookmark in the nonclustered indexwill be kept in the same order as the clustered index?Here's an example to demonstrate my question:CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3int,col4 int)ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)CREATE INDEX ix_indextest ON indextest (col1,col3)GOINSERT indextest VALUES (1,2,1,1)INSERT indextest VALUES (1,3,2,1)INSERT indextest VALUES (1,4,2,1)INSERT indextest VALUES (2,1,1,1)INSERT indextest VALUES (1,1,1,1)SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1DROP TABLE indextestThe select statement above is covered by the nonclustered index, sothat index is used. However, the nonclustered index is defined only toensure the ordering of col1 and col3 within the index; col1 and col2follow within the index as the bookmark to the clustered index. When Irun this query, my desired result is to have the records appear in theorder supported by the clustered index:1,11,2As it happens, the result I got was indeed in that order, but I don'tknow if it was mere coincidence, or if the bookmark in the nonclusteredindex is maintained in the same order as the clustered index. If Iwant to ensure the above order, is it sufficient to have thenonclustered index defined as above, or do I need to define it as:create index ix_indextest on indextest (col1,col3,col2)just to be sure that the results are returned in ascending order forcol1,col2? If the two-column index is sufficient, is it guaranteed tostill be sufficient in SQL2005 and future versions of SQL Server, or amI better off adding the third column just to be safe?Thank you,--Dennis Culley

View 4 Replies View Related

Specific Date With In An Ordered Sequence

Mar 7, 2008


How would i find the 3rd date in a sequence of dates, that is ordered?
Example
Date1 = 08-01-01
Date2 = 08-02-01
Date3 = 08-03-01
Date4 = 08-04-01

i need to retrieve Date3

View 4 Replies View Related

Can A DELETE's OUTPUT Rowset Be Ordered?

Oct 19, 2007

I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur in the same order as the clustering key of the target table. Is this possible?

View 6 Replies View Related

Return Set Ordered By Physical Order Of Rows?

Nov 5, 2012

I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.

Specifically, something like:

Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"

I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.

(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).

View 6 Replies View Related

Storing Large Arrays Of Ordered Pairs

May 13, 2008

How do you all recommend storing ordered pairs in SQL Server 2005? I plan to add one record for every data point but this will generate many records and requires an extra field to relate the points together. Are there any better ways to do this? Can the data still be searchable or does it have to be unpacked first?

View 2 Replies View Related

Return The Data From A Table Ordered By Its Hierarchy

May 3, 2006

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2


How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

View 9 Replies View Related

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement

May 15, 2008

Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.


ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END



ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View 14 Replies View Related

Exclusive Mode

May 21, 2003

Hi,

When I open an application, it prompts me for a message that SQL is locked in exclusive mode by other application.

How to solve this?

thanks in advance

christine

View 3 Replies View Related

Non-bitwise Exclusive Or?

Jan 30, 2004

Is there a way to do a logical exclusive OR (XOR) in sql server?

I'm trying to do this in where clause, something like:

WHERE
(not exists (select 1 from table a where a.date > '01/30/03') XOR
exists (select 1 from table a where a.date < '01/30/03'))

Thanks!

View 14 Replies View Related

Exclusive Locks

Jun 11, 2008

Hi,

How do you lock a table in exclusie mode before running a query?

thanks,

View 5 Replies View Related

Many To Many - Exclusive Result

Oct 17, 2007

A problem about many to many relationships from an SQL beginner. Here's a contrived abstract example, as I'd prefer not to give away specifics.


Imagine I have two tables: users, food
The relationship (to like) is many-to-many so I've got a link table, which might look like the below:

andrew, apples
bob, banana
bob, apples
chris, carrots
chris, apples
chris, banana


I want to select users who like bananas and apples exclusively.

The answer should be 'bob' ONLY. select * from users inner join food on <IDs> where food in ('bananas','apples') isn't suitable , because it'll also return 'chris' who should be disqualified (because he also likes carrots).


Apart from potentially being bad DB design (although this is an abstract example; I do have ID numbers), can anyone suggest how to get this in a scalable way?

View 8 Replies View Related

Exclusive Lock During A SELECT

Aug 9, 2000

Anybody know how a SELECT statement can generate an exclusive lock on a table ?
I always thought that SELECT's take out shared locks. Is this something to do with temporary tables generated by ORDER BY's and DISTINCT ?
Rogue SQL below (from Site Server).

SELECT A.i_Dsid, A.i_Aid, A.vc_Val, A.i_Val, A.dt_Val, A.img_Val FROM Object_Attributes A, ( SELECT DISTINCT L.i_Dsid FROM Object_Lookup L , Object_Attributes OA2 (index = IND_vc_Aid) WHERE ((( L.i_ObjectClass = 9999 )) AND ( OA2.vc_Val LIKE ( '999999999.9999999%' ) AND OA2.i_Aid = 99)) AND (L.i_Container_Dsid = 99) AND ( OA2.i_Dsid = L.i_Dsid )) AS B WHERE B.i_Dsid = A.i_Dsid AND A.i_Aid NOT IN( 1, 2, 3, 4, 5 ) ORDER BY A.i_Dsid, A.i_Aid

Can anybody suggest a workaround ? Thanks.

View 2 Replies View Related

How To Obtain Exclusive Access Via SQL

Feb 5, 2003

Hi,

i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:

"Exclusive access could not be obtained because the database is in use. ...."

how do i gain this Exclusive use via an SQL statement please?

View 8 Replies View Related

Exclusive Access To DataBase

Jul 12, 2004

Hi.

I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?

(SQLServer 2000)

thanks.

Francisco

View 2 Replies View Related

Trying To Get Exclusive Access To A DB For Restore.

Feb 11, 2004

I have created a SQL Agent job that is supposed to essentially duplicate a production database to another database. The code I am using is:

step1
__________________________________________________ ______
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)
from sysprocesses where dbid=12

--Print (@SQL)
exec(@sql)

step2
__________________________________________________ ________

RESTORE DATABASE HIWDYNARPT FROM PRDBACKUP
WITH REPLACE
__________________________________________________ ______

This works when I test it during the day, however when it runs at night I get the following error in the job log:

Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I'm not sure why this happens because I have killed all open threads in step 1, and then create my own new thread in step two. Maybe someone else is initiating a new thread to quickly between the steps???

Anyway, I am trying to use:
__________________________________________________ __
ALTER DATABASE HIWDYNARPT
RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
__________________________________________________ ____

...as an alternative to the T-SQL killing PID's, but SQL 7.0 SP4 does not seem to support restricted user like 2000. It keeps giving me a syntax error. Does anyone have any suggestions?

If I bring step 1 and step 2 together, separated by "GO", could this fix the problem?

Thanks in advance!

Ryan Hunt

View 5 Replies View Related

Exclusive Access Could Not Be Obtained Because The

Dec 8, 2007

Could anybody give a lead as to what I can do get rid off this error please.

I alread tried following:
use master
go
Alter Database dbname set single_user with rollback immediate;
go

Still have the issue. SQL 2005 Server actually did lock the db.
So ran
Alter Database dbname set multi_user;
go
and refresh Query and it switch back to multi user.
But I can't restore db yet.

Thank you

View 1 Replies View Related

Intent Exclusive Locks

Jan 4, 2008



Are Intent exclusive locks compatible with rowlock?

I am getting a deadlock since i have ix lock on a page and another process(select query) is trying to acquire a shared lock.How can i solve this?

View 3 Replies View Related

Exclusive Insert Lock On A Table

Sep 10, 2007

Hello All!
I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.
So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?
Thanks!
David
 

View 9 Replies View Related

Exclusive Access Could Not Be Obtained Because The Database Is In Use.

May 25, 2004

How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.

I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??

Waiting for your earliest replies

View 1 Replies View Related

Can You Place An Exclusive Row Lock Using A Hint?

Feb 21, 2000

Hi,

Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).

Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.

Any suggestions on whether this is possible would be welcome.

Thanks,

Karl

View 2 Replies View Related

Query Trouble Regarding Bitwise Exclusive

Feb 25, 2001

HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....


SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA


.5 is where the query bombs. any idea why is this happenning?

by the way, i'm using sql server 7.0.

francis,

View 2 Replies View Related

Exclusive Access Could Not Be Obtained Because The Database Is In Use

May 31, 2008

Hi!

We're using a backup with sql server agent when doing a backup / restore
procedure. In some cases I get the following error when the restore job
fails:

Executed as user: DOMAINAdministrator. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error 3101)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.

Is it related to unhandled lock? I've resolved this by restarting the sql
server. But is there a way to avoid such issues?
One more question. Is it OK to backup/restore database while there're users
connected? Or I can do only backup?

View 2 Replies View Related







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