Can not delete data from View

I have a view is made from three tables by UNION ALL.
When I tried to delete some records by using 'Delete from My_New_View_2004..
.', I get error:
Server: Msg 4440, Level 16, State 9, Line 1
UNION ALL view is not updatable
Why and how to fix this problem so I can delete data from view?
Thanks for help.
-GeorgeA view containing a UNION is not updateable unless it can meet the criteria
for a Partitioned View. See the Partitioned Views topic in Books Online for
details.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sICdnfm2y_xb7_7dRVn-ig@.giganews.com...
> A view containing a UNION is not updateable unless it can meet the
criteria
> for a Partitioned View. See the Partitioned Views topic in Books Online
for
> details.
Alternatively, if you are running SQL 2000 you can have an INSTEAD OF DELETE
trigger that deletes from the underlying tables
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004

Can not create views, tables etc * URGENT*

Hi,

I am not getting option as 'new view' and 'new table' when I right
click on
views and tables option in VisualStudio.net IDE server explorer
sqlservers database to create new objects.

Looks like some setup issue in my database.

Thanks for your help in advance.

Rgds
CVCV (chaitu_m7@.yahoo.co.in) writes:
> I am not getting option as 'new view' and 'new table' when I right
> click on
> views and tables option in VisualStudio.net IDE server explorer
> sqlservers database to create new objects.
> Looks like some setup issue in my database.

Does this happen in every database, including tempdb? How do you connect
to the server? I don't use VS .Net to access SQL Server normally, so I
don't have much idea what it could be, but one possibility is that the
you don't have rights to create new objects in the database in question.

Personally, I prefer to use Query Analyzer to create tables, using
the CREATE TABLE statement, and this is also what I recommend in general.
In the long run, you are more effective with this than using a graphic
tool. Also, this is the only option if you want to put you tables under
source control - which you should.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Can not connect to some tables with seagate crystal report

Seagate crystal reports:
When i am trying to attach a table to seagate crystal report i am getting
this error:
Database Error: [Microsoft][ODBC SQL Server Driver][SQL
Server][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
I can connect to other tables in same database. One thing that is different
in the tables which i can not connect is that its name is like (Application
Name.dbo.samplecounty-etp.etp.Emp_Qualifications). Can naming syntex of table
cause connection issue or its a permission issue?
ontario, canada
SQL server 2000 enterprise edition. The problem is with the table name.
Somehow crystal report does not accept table name with "."
Does anyone know a wayaround.
ontario, canada
"db" wrote:

> Seagate crystal reports:
> When i am trying to attach a table to seagate crystal report i am getting
> this error:
> Database Error: [Microsoft][ODBC SQL Server Driver][SQL
> Server][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied.
> I can connect to other tables in same database. One thing that is different
> in the tables which i can not connect is that its name is like (Application
> Name.dbo.samplecounty-etp.etp.Emp_Qualifications). Can naming syntex of table
> cause connection issue or its a permission issue?
>
> --
> ontario, canada
|||"db" <db@.discussions.microsoft.com> wrote in message
news:6013B66D-ECA6-4B76-8480-B72E5ED7DC47@.microsoft.com...
> SQL server 2000 enterprise edition. The problem is with the table name.
> Somehow crystal report does not accept table name with "."
> Does anyone know a wayaround.
> --
> ontario, canada
Rename the table? Create/use a view with a name that Crystal likes? Or
perhaps post on a newsgroup that is specific to crystal reports. You do
know that Seagate is no longer involved with Crystal Reports and hasn't been
for some years - that would indicate you are using an old (and perhaps
unsupported) version.
|||Views works.
Thanks
"Scott Morris" wrote:

> "db" <db@.discussions.microsoft.com> wrote in message
> news:6013B66D-ECA6-4B76-8480-B72E5ED7DC47@.microsoft.com...
> Rename the table? Create/use a view with a name that Crystal likes? Or
> perhaps post on a newsgroup that is specific to crystal reports. You do
> know that Seagate is no longer involved with Crystal Reports and hasn't been
> for some years - that would indicate you are using an old (and perhaps
> unsupported) version.
>
>

Can not connect to some tables with seagate crystal report

Seagate crystal reports:
When i am trying to attach a table to seagate crystal report i am getting
this error:
Database Error: [Microsoft][ODBC SQL Server Driver][SQL
Server][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not ex
ist or
access denied.
I can connect to other tables in same database. One thing that is different
in the tables which i can not connect is that its name is like (Application
Name.dbo.samplecounty-etp.etp.Emp_Qualifications). Can naming syntex of tabl
e
cause connection issue or its a permission issue?
ontario, canadaSQL server 2000 enterprise edition. The problem is with the table name.
Somehow crystal report does not accept table name with "."
Does anyone know a wayaround.
--
ontario, canada
"db" wrote:

> Seagate crystal reports:
> When i am trying to attach a table to seagate crystal report i am getting
> this error:
> Database Error: [Microsoft][ODBC SQL Server Driver][SQL
> Server][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
> access denied.
> I can connect to other tables in same database. One thing that is differen
t
> in the tables which i can not connect is that its name is like (Applicatio
n
> Name.dbo.samplecounty-etp.etp.Emp_Qualifications). Can naming syntex of ta
ble
> cause connection issue or its a permission issue?
>
> --
> ontario, canada|||"db" <db@.discussions.microsoft.com> wrote in message
news:6013B66D-ECA6-4B76-8480-B72E5ED7DC47@.microsoft.com...
> SQL server 2000 enterprise edition. The problem is with the table name.
> Somehow crystal report does not accept table name with "."
> Does anyone know a wayaround.
> --
> ontario, canada
Rename the table? Create/use a view with a name that Crystal likes? Or
perhaps post on a newsgroup that is specific to crystal reports. You do
know that Seagate is no longer involved with Crystal Reports and hasn't been
for some years - that would indicate you are using an old (and perhaps
unsupported) version.|||Views works.
Thanks
"Scott Morris" wrote:

> "db" <db@.discussions.microsoft.com> wrote in message
> news:6013B66D-ECA6-4B76-8480-B72E5ED7DC47@.microsoft.com...
> Rename the table? Create/use a view with a name that Crystal likes? Or
> perhaps post on a newsgroup that is specific to crystal reports. You do
> know that Seagate is no longer involved with Crystal Reports and hasn't be
en
> for some years - that would indicate you are using an old (and perhaps
> unsupported) version.
>
>

Can not connect to Database

I recently uninstalled SQL Server due to connection issues I started having. I could start the DB and connect/edit tables in Ent Mgr etc, but all the sudden, none of my code could make DB connections.

So, after reinstalling now nothing works. I cannot even connect in Ent. Manager. I went to Administrative Tools->Services to make sure it was running, and SQLSERVER isn't even listed under services. What happened? Is there another step after installing SQL Server I missed to get the service up and going. Do I need to do something in ODBC connections?

I am on Windows XP with SP2.

TimWell see if SQL Server Manager is running in the system tray. Maybe you didn't set it to start SQL Server on windows startup. Also if you did reinstall I would think your sa password is blank unless you set it during install.|||It is not running in the system tray, and I cannot start SQL Server, because the service does not exist. Setting it to start on windows startup doesn't do anything.

For some reason when I install it, the service component does not get installed.

I get the following error when I try to start SQL Server

--------
Service Control Failure
"An Error 1060 - (The specified service does not exist as an installed service) occured while performing this operation on the MSSQL service"|||Try reinstall with the repair option and make SQL Server Service Auto start, SQL Server will start anytime you start the OS. Hope this helps.

Kind regards,
Gift Peddie|||I do not see where I can install with "repair" mode. That option never comes up.

However, this happens every time I try to install. I get an error window that pops up at the start that say this:

"Microsoft SQL Server Standard Edition server component is not supported on this operating system. Only client components will be available for installation."

I am on Windows XP Service Pack 2. I don't understand though, because I installed it fine on this computer before. But maybe it was before I installed SP 2, I dont know.

Anyone know why this is happening or what I should do?|||Use the original cd you used when installing on that box. And don't try to say that you used that exact cd when you first installed because it's not supported on client operating systems.|||We have the exact same problem
We have the original cd's of the Standard, Developer & Personal editions.
After installing the developers edition on WindowsXP, the service manager app shows in the taskbar, but it will not connect to the sql server|||try installing service pack 3|||hi all

I installing service pack 3 and NO fix't|||XGBoomer, yours is NOT the exact same problem as you actually do have SQL Server installed and it is running.

What exactly do you mean by "it" in "it will not connect to the sql server"? And, are you receiving any error messages?

Terri|||You can only install the developer or MSDE versions on XP. You can't put Enterprise or Standard on the OS. Install developer and you should be good to go. I'm not sure what you've done to your DB's now though. YOu may want to look in the data folder and copy your files to another location, if they are still there. Then if it hoses your data directory at least you will still be able to attach the databases back to the new install.

hth Bruce|||Not quiet the same problem but I have Developer edition on XP Pro/SP2 running for a while now. Out of the blue it won't run ie it starts and then stops straight away. There is another edition of MSDE on the same box that runs fine, and I don't think its the firewall.

Any ideas?

Regards|||Update - I tried the repair function (using the SQL installer) which did the trick. I can't figure out why it happened though.

Regards

Can not browse sql 2000 tables from SSMS

Hello,

I get an error message( see below) when I try to browse tables of a SQL2k server from SSMS, but I can browse those tables in sql query analyzer with no problems.

Do I need to change any settings in SSMS to work around this error?

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

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

BUTTONS:

OK

thanks,
Saptagiri

Please file a defect report for this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

Reports filed on the feedback center are used to prioritize work in future versions and service packs. You can even vote for other the bugs reports filed by other people. The more votes a defect gets, the higher its priority is for being fixed.

Thanks,
Steve

Can no longer get dataset fill method to work after directory copy

I have been creating a small winforms app using VS 2005 and SQL Express. I
create serveral tables in the database and added it as the datasource for th
e
project. I created several forms, created several datagrids and regular
design views. After I got several of the forms running, I decided to backup
my directory and copied the entire directory to a backup directory on my har
d
drive.
While continuing to work on my project, the IDE lost some of the data
adapters and and datasets while I was switching between the design view and
code view. So I decided to delete the directory and copy the backup I had
saved and start from there. But a Big Problem occured. Once I opned the
project and tried to execute it, I could no longer get the app to connect to
the database. I am the only user and administrator account but every time th
e
app tries to fill a table now I get on of these two erors:
System.Data.SqlClient.SqlException was unhandled
Message="Unable to open the physical file -- ". Operating system error
5: \"5(Access is denied.)\".\r\nUnable to open the physical file
or
System.Data.SqlClient.SqlException was unhandled
Message="Cannot open user default database. Login failed.\r\nLogin failed
for user
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=11
LineNumber=65536
Number=4064
Procedure=""
Server=".\\SQLEXPRESS"
State=1
StackTrace:
I can no longer connect to the database even thou I can see and work with it
in the IDE. Help
HWDotNetDeveloperSeveral possible reasons for this:
1) you did not restore your original LDF file with your matching MDF
2) you have not re-attached the database
3) you are trying to connect using an sql user account and it is not config
ured the same with the "backed" up version
4) you are trying to connect without the correct user instance reference (i
e. the backed up version is different from the one you deleted)
If you can see the database from the IDE, then using query analyzer and run
a test query. You can also start OSQL and run a simple query against the da
tabase. If these queries work, then there is something not synchronized wit
hin your code versus the database that was restored.
Check out
http://msdn.microsoft.com/library/d...>
userinst.asp
Hope this helps...
-Eric

Can Nhibernate create tables into my db?

Hello all;
I'm so new in NHibernate and I wonder that "can nhibernate create tables into my db"?
If it can; how?
Which confugrations are needed?
Thanks...

Seems like you should be asking this in an NHibernate forum. I am not sure what the product does but doing some research on the web I found link to their documentation.

http://www.hibernate.org/hib_docs/v3/reference/en/html/

HTH

Can Multiple Selection Controls be use with SqlDataSource?

 NET 2.0 

I am using Visual Studio Express 2005 for database web developement.

I have created a database with 5 tables. Two are associative tables.

They are

Software
PK SoftwareID
Title

SoftwareSolution
FK SoftwareID
FK SolutionID

Solution
PK SolutionID
Title

CategorySolution
FK Category
FK Solution

Category

PK Category
Title

Criteria for a search with three sources of input into a SqlDataSource attached to a FormView for paged out.

The following are the Search Criteria input sources:
1) ListBox in Multiple Selection Mode
2) CheckBoxList with Mutiple checks posible
2) TextBox with key word search in Title.

Each Solution has 0 or many Categories and 0 or many Software.

When the records are entered in the database, the associative information for Categories and Software are populated to filter the
returned values.

In NET 1.1, I would create a search string like the following in code behind by looping through the CheckBoxList and ListBox finally adding the TextBox:

SELECT Solution.SolutionID, Solution.Title
FROM Solution
WHERE
Solution.SolutionID IN ('2','3','5')
AND
Solution.Title LIKE '%Math%'

I am unable to update the SelectCommand in the ASPX page.

However, when I use the I have used the
System.Web.UI.WebControls.SqlDataSources
"SelectQuery Builder"
I receive the following in the Source for ASPX:

 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT Solution.SolutionID, Solution.Title FROM Solution INNER JOIN CategorySolution ON Solution.SolutionID = CategorySolution.SolutionID INNER JOIN SoftwareSolution ON Solution.SolutionID = SoftwareSolution.SolutionID"> <SelectParameters> <asp:ControlParameter ControlID="CheckBoxList1" DefaultValue="%" Name="CategoryID" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="ListBox1" DefaultValue="%" Name="SoftwareID" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="txtTitleSearch" DefaultValue="%" Name="Title" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource>
 

I receive duplicate records and not sure if the code generated accesses the Mutiple Selected values in the CheckBoxList1 and ListBox1.

Question:

1) If the SelectQuery generator will not access multiple selected values from the CheckBoxList1, how can I use code behind to replace

SelectCommand in the ASPX page?

2) What is the best solution for using the CheckBoxList1, ListBox1, and TextBox for filtering my results?

 Sample code, references to solutions, corrections on logic, a new approach, how to use the wizard correctly would all be greatly appreciated.

3) Can Multiple Selection Controls be use with a SqlDataSource SELECT Command?

Thanks for your time,

Sincerely,

Unhistoric

Not, these are a summary of my unsuccessful results and may have a few unintentional syntax errors as a result of the interface of publishing on this website.

After further testing, I have not found a solution upto this point.

I have tried using a SessionParameter of type string to update ('4','20') in the following code in my ASPX file:

I set session variables in the Page_PreInit on the ASPX.VB for testing purposes.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Solution] @.inputSolutionID"> <SelectParameters> <asp:SessionParameter Name="inputSolutionID" SessionField="SolutionSearch" type="String" /> </SelectParameters></asp:SqlDataSource>

Only Works with: session("SolutionSearch") ="" resulting in unfiltered results.

   -----------------------------------------------------------

  
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Solution] WHERE ((CAST([SolutionID] AS Nvarchar)) IN (@.inputSolutionID))"> <SelectParameters> <asp:SessionParameter Name="inputSolutionID" SessionField="SolutionSearch" type="String" /> </SelectParameters></asp:SqlDataSource>
 Only Works with a single assignment: session("SolutionSearch") ="'1'" resulting in result and not with = "'1','20'"

------------------------------------------------------------- 

The following static code works:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT * FROM [Solution] WHERE ([SolutionID] IN ('4', '20'))"> </asp:SqlDataSource>

 ****Is there a way to dynamically update the proceeding from code ASPX.VB behind?**************

Sincerely frustrated,

Unhistoric

|||

The answer to the problem was resolved with the following link:

http://forums.asp.net/thread/1604679.aspx

It was pointed out the SelectCommand could be set from the code behind in the asp.vb. The key was to leave out the SelectCommand in the "ASPX" code file and set the initial SelectCommand in the Page_Load Event. From my understanding, I was not able to set the SelectCommand initially in my tests due to the fact it existed in part in the "ASPX" code.

Special Thanks to "Yani Dzhurov",

Sincerely,

Unhistoric

Can Msde import DBF files in my tables

Hi Sergio,
This took me a while to figure out, but it is really simple. From my
message a few weeks ago...
"Just to update people (and for my future reference when I search Google
for my name), using DTS makes it VERY simple to import DBF files into SQL.
What screwed me up was two things:
1) While it may be possible to do in MSDE, it is very simple using DTS
on the server. I logged into my client's SQL server and ran DTS, filled
out a few options and imported all the FoxPro info into a new SQL database.
2) DTS is actually found via: Start Program > Microsoft SQL Server >
Import Export Data ... this launches the DTS wizard. I was looking
specifically for something called DTS. I should have tried the
import/export option first"
Hope this helps
Richard
Sergio wrote:

> Hi, y really now if its possible in MSDE,
> can i import DBF files in my tables of MSDE?, how?, i need import in Tsql,
> how?
> thanks
Yes, it's easy in DTS but you can also save a DTS package that can then be
run in the MSDE.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:uX2K49CHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi Sergio,
> This took me a while to figure out, but it is really simple. From my
> message a few weeks ago...
> "Just to update people (and for my future reference when I search Google
> for my name), using DTS makes it VERY simple to import DBF files into SQL.
> What screwed me up was two things:
> 1) While it may be possible to do in MSDE, it is very simple using DTS
> on the server. I logged into my client's SQL server and ran DTS, filled
> out a few options and imported all the FoxPro info into a new SQL
database.
> 2) DTS is actually found via: Start Program > Microsoft SQL Server >
> Import Export Data ... this launches the DTS wizard. I was looking
> specifically for something called DTS. I should have tried the
> import/export option first"
>
> Hope this helps
> Richard
> Sergio wrote:
Tsql,

Can it Be done?

I currently use Access 2000 as my Backend DB....I use Access as mearly a datadumb...there is nothing but tables, relationships and indexs in the DB. I use VB6 as my front end.

My question is....Can the same thing be done with SQL Server Express 2005?

hi,

yes, as SQL Server\SQLExpress really are a database and not an "environment" as Access is/can be...

in SQL Server\SQLExpress the database part is the one responsible for storing "data", and the vb6/vb.net/c#/aspnet/... is responsibible presenting the data to the end users..

it's ok

regards

|||Thank you very much for your response....i think SQL Server 2005 Express will be a better FREE option for my backend Datadump.

Can it Be done?

I currently use Access 2000 as my Backend DB....I use Access as mearly a datadumb...there is nothing but tables, relationships and indexs in the DB. I use VB6 as my front end.

My question is....Can the same thing be done with SQL Server Express 2005?

hi,

yes, as SQL Server\SQLExpress really are a database and not an "environment" as Access is/can be...

in SQL Server\SQLExpress the database part is the one responsible for storing "data", and the vb6/vb.net/c#/aspnet/... is responsibible presenting the data to the end users..

it's ok

regards

|||Thank you very much for your response....i think SQL Server 2005 Express will be a better FREE option for my backend Datadump.

Can I use Top here and I'm stuck on how to get the results I'm looking for

The tables and queries are below with comments on what I'm trying to
accomplish. Thanks to all !
Copying from the next line to the end and pasting into QA does it all
(except what I'm looking for)
set nocount on
--drop table combinations
--drop table results
create table combinations (id int, combination int, innerindex int, value
int)
-- the combination field indicates a 2 number combination
-- although this just a sampling of my combinations table,
-- it holds 3,4 and 5 number combinations as well
-- innerindex is for my testing and is used below
-- value is the actual number from the 2 number combination
insert into combinations values(1,2,1,1)
insert into combinations values(1,2,2,2)
insert into combinations values(2,2,1,1)
insert into combinations values(2,2,2,3)
insert into combinations values(3,2,1,1)
insert into combinations values(3,2,2,4)
insert into combinations values(4,2,1,1)
insert into combinations values(4,2,2,5)
insert into combinations values(5,2,1,2)
insert into combinations values(5,2,2,3)
insert into combinations values(6,2,1,2)
insert into combinations values(6,2,2,4)
go
create table results (id int, testsetid int)
-- the results table lists which testsets (from another table)
-- have which combination. So for the first record
-- testset 50 contains the combination 1,2 as indicated
-- by the id of 1
insert into results values(5,50)
insert into results values(1,2)
insert into results values(6,50)
insert into results values(1,2)
insert into results values(4,450)
insert into results values(1,2)
insert into results values(3,150)
insert into results values(1,2)
go
-- this works nicely to give me a total of the results grouped by the
combinations
select id, count(*) as Total from results group by id order by count(*) desc
-- Is there a way to use the Top function to only
-- give me the top 1 or 5 or 5 percent
select a.id, count(*) as Total from results a group by a.id
having a.id in (select id from combinations where combination = 2)
order by total desc
-- here I am trying to get a Total and the actual combination listed
horizontally
-- but I don't see how. I was reading that this type of thing should be done
in the
-- presentation layer but I'm not sure how I can do it there either.
-- I'm using c# and can relate the tables via a dataset but I;m still stuck
-- any suggestions would be appreciated.
select a.id, count(*) as Total,
SUM(CASE [innerindex] WHEN 1 THEN value ELSE ' ' END)AS F1,
SUM(CASE [innerindex] WHEN 2 THEN value ELSE ' ' END)AS F2,
SUM(CASE [innerindex] WHEN 3 THEN value ELSE ' ' END)AS F3,
SUM(CASE [innerindex] WHEN 4 THEN value ELSE ' ' END)AS F4,
SUM(CASE [innerindex] WHEN 5 THEN value ELSE ' ' END)AS F5
from combinations a join results b on a.id = b.id
group by a.id
order by a.id
-- the ultimate goal is to have something that looks like
--
-- Total Combination
-- 55 1,3,8,55
-- 10 1,3
-- 8 1,9
-- and if I could say show me the top 3 number combinations or
-- top 5 percent of all combinations etc etc
-- thanks alot!!!!
drop table combinations
drop table resultsOn Tue, 17 May 2005 11:03:10 -0400, D wrote:

>The tables and queries are below with comments on what I'm trying to
>accomplish. Thanks to all !
>Copying from the next line to the end and pasting into QA does it all
>(except what I'm looking for)
(snip)
Hi D,
First: thanks for posting the CREATE TABLE and INSERT statements and the
requested output.
Unfortunately, it didn't help in this case - I am still at a loss when I
try to grok what you're trying to do.
It might help if your script included the constraints as well. The
tables you posted have no primary key, and there is no foreign key to
indicate the relationship either.
For a better understanding, a short explanation of the business problem
you're trying to solve might help lots too.
Finally, I really don't see how the required rersults you posted relate
to the sample data in the INSERT statements. Could you elaborate on
that?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hey Hugo hows it going?
This code was actually based upon some of your examples that you showed me
in an earlier post I had. At the time I was trying to search for
combinations within a table and you had suggested I create a normalized
table of combinations and join in the data table to seek out the
combinations. I have attached it at the very end of this message in case you
wish to review, look for 'Start of old post' below.
I created the table of combinations (called combinations) and I have my
table of data then I was running the join that you gave me but I was
concerned that it was taking a long time to complete. I then changed the sql
select to 'SELECT w.combination, t.testset into results' to save the results
except I renamed combination to id and testset to testsetid.
So in the 'Query Analyzer Section' below, the table 'Results' is the actual
output from the join query in the section 'Start of old post' and there
isn't any keys set because I was just doing a select into
(I changed some names and text to make it more clear)
============== Start of Query Analyzer Section
====================================
set nocount on
--drop table Combinations
--drop table Results
create table Combinations
(CombinationId int, Combination int, InnerIndex int, CombinationValue int,
primary key(CombinationId, Combination, InnerIndex) )
-- CombinationId = Identity field
-- Combination = field indicates a 2 number combination
-- although this just a sampling of my Combinations table,
-- it holds 3,4 and 5 number Combinations as well
-- InnerIndex = for my testing and is used below
-- CombinationValue = is a number from the 2 number combination
insert into Combinations values(1,2,1,1)
insert into Combinations values(1,2,2,2)
insert into Combinations values(2,2,1,1)
insert into Combinations values(2,2,2,3)
insert into Combinations values(3,2,1,1)
insert into Combinations values(3,2,2,4)
insert into Combinations values(4,2,1,1)
insert into Combinations values(4,2,2,5)
insert into Combinations values(5,2,1,2)
insert into Combinations values(5,2,2,3)
insert into Combinations values(6,2,1,2)
insert into Combinations values(6,2,2,4)
go
create table Results (CombinationId int, TestsetId int)
-- The Results table lists the CombinationId's
-- which you could consider a foreign key
-- that originates from the Combinations table
-- and the Testsetid
-- which you could consider a foreign key
-- that originates from my data table not shown
-- For the records below
-- Testsetid 1 contains the combination 1,2 as indicated
-- by the Combinationid of 1
insert into Results values(1,1)
-- Testsetid 50 contains the combination 2,4 as indicated
-- by the Combinationid of 6
insert into Results values(5,50)
insert into Results values(6,50)
insert into Results values(1,2)
insert into Results values(4,450)
insert into Results values(1,3)
insert into Results values(3,150)
insert into Results values(1,4)
insert into Results values(2,1)
insert into Results values(2,2)
insert into Results values(2,3)
insert into Results values(2,4)
insert into Results values(2,5)
insert into Results values(2,6)
go
-- here I found the solution that I was looking for
-- **see comments in post about this
--
-- the top row of the Results is
-- 6 1 3 0 0 0
-- which reads there are 6 Combinations of 1 & 3
-- the next row reads there are 4 Combinations of 1 & 2
select
(
select count(*) from Results b where a.CombinationId = b.CombinationId
group by b.CombinationId
) [Total Combinations],
SUM(CASE [InnerIndex] WHEN 1 THEN CombinationValue ELSE ' ' END)AS [
First
Number],
SUM(CASE [InnerIndex] WHEN 2 THEN CombinationValue ELSE ' ' END)AS [
Second
Number],
SUM(CASE [InnerIndex] WHEN 3 THEN CombinationValue ELSE ' ' END)AS [
Third
Number],
SUM(CASE [InnerIndex] WHEN 4 THEN CombinationValue ELSE ' ' END)AS [
Fourth
Number],
SUM(CASE [InnerIndex] WHEN 5 THEN CombinationValue ELSE ' ' END)AS [
Fifth
Number]
from Combinations a
group by a.CombinationId
having a.CombinationId in
(
-- using this I can select the combination I want
-- this says only show me the Results from the 2 number Combinations
select a.CombinationId from Results a
group by a.CombinationId
having a.CombinationId in (select CombinationId from Combinations where
Combination = 2)
-- using this count I can control some of the Results
and count(*) > 1
)
order by [Total Combinations] desc
-- thanks alot!!!!
drop table Combinations
drop table Results
============== End of Query Analyzer Section
====================================
*** Comments about my large query
It works good for small sets like these but if I set it to search from a 4
or 5 number combinations from my results table
which contains 37960 records, it just slowly chugs away for a long long
time.
I was hoping to just get back the top 5 or 10 combinations and their totals
but sql has to run every combination to figure out
the top totals.
Thanks for your help as always!!!!
============== Start of old post ====================================
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
============== End of old post ====================================|||On Thu, 19 May 2005 11:15:42 -0400, D wrote:

>Hey Hugo hows it going?
Hi D,
I'm fine, thanks!
(BTW, sorry to have kept you waiting - I needed some time for this, and
real life kept interfering).

>This code was actually based upon some of your examples that you showed me
>in an earlier post I had. At the time I was trying to search for
>combinations within a table and you had suggested I create a normalized
>table of combinations and join in the data table to seek out the
>combinations. I have attached it at the very end of this message in case yo
u
>wish to review, look for 'Start of old post' below.
Thanks. I didn't recognise it from your earlier post, but now I do
recall having that discussion with you (though I don't recall what
exactly your original question was - but I think that's not really
relevant to this question, right?)

>I created the table of combinations (called combinations) and I have my
>table of data then I was running the join that you gave me but I was
>concerned that it was taking a long time to complete. I then changed the sq
l
>select to 'SELECT w.combination, t.testset into results' to save the result
s
>except I renamed combination to id and testset to testsetid.
>So in the 'Query Analyzer Section' below, the table 'Results' is the actual
>output from the join query in the section 'Start of old post' and there
>isn't any keys set because I was just doing a select into
I have to disagree - in a proper table, there ALWAYS are keys. They may
not be defined, and therefor unknown to the server, but LOGICALLY, the
keys exist.
Knowing the keys (the LOGICAL keys, that is) is important when querying
a table. Keys tell you what columns to use in filtering, joining, or
grouping. They tell you when to expect duplicates. Etc.
For the record - you can add keys to a table that is populated with
SELECT INTO. Whether or not you do that has no consequence for the logic
of the query. It will change the performance of all queries and
statements involving the table. It can also change the way unexpected
bad data is handled.
(snip)
>*** Comments about my large query
>It works good for small sets like these but if I set it to search from a 4
>or 5 number combinations from my results table
>which contains 37960 records, it just slowly chugs away for a long long
>time.
>I was hoping to just get back the top 5 or 10 combinations and their totals
>but sql has to run every combination to figure out
>the top totals.
>Thanks for your help as always!!!!
I must admit that I had some trouble understanding what you did. I'm
still unsure if I understand your query completely. I did manage to
write two alternatives that both produce the same results from the
sample data you provided, but do test it against some other test sets as
well, as I might have misinterpreted the logic of your query.
-- Alternative #1
SELECT Total AS [Total Combination],
First AS [First Number],
Second AS [Second Number],
Third AS [Third Number],
Fourth AS [Fourth Number],
Fifth AS [Fifth Number]
FROM (SELECT (SELECT COUNT(*)
FROM Results AS b
WHERE b.CombinationId = a.CombinationId) Total,
SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
0 END) AS First,
SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
0 END) AS Second,
SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
0 END) AS Third,
SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
0 END) AS Fourth,
SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
0 END) AS Fifth
FROM Combinations a
WHERE Combination = 2
GROUP BY a.CombinationId) AS x
WHERE Total > 1
ORDER BY Total DESC
-- Alternative #2
SELECT MAX(b.Total) AS [Total Combination],
SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
END) AS [First Number],
SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
END) AS [Second Number],
SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
END) AS [Third Number],
SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
END) AS [Fourth Number],
SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
END) AS [Fifth Number]
FROM Combinations a
INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
FROM Results
GROUP BY CombinationId
HAVING COUNT(*) > 1) AS b
ON b.CombinationId = a.CombinationId
WHERE a.Combination = 2
GROUP BY a.CombinationId
ORDER BY SUM(b.Total) DESC
go
If my queries are correct, the next step is to do some performance
tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
get a quick overview. But the best way to test is to use an "empty"
server (ie no other processes running), clear the cache, then measure
the time taken to run one of the queries. Like this:
CHECKPOINT -- Write dirty pages to disk
DBCC FREEPROCCACHE -- Free procedure cache
DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
go
DECLARE @.strt datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert query to be tested here.
-- Use SELECT .. INTO #Tmp if result set is large,
-- to eliminate network speed from the test
SET @.end = CURRENT_TIMESTAMP
SELECT @.strt AS started, @.end AS ended,
DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I can't thank you enough for your help.
I haven't fully tested the results but they look good initially. Two quick
tests showed that alternative #2 was under a second and #1 took roughly 30
seconds. I'll have more comments and perhaps some questions later but I just
wanted to stop in here and thank you again because those are both brilliant
solutions and have expanded my sql knowledge enormously.
Best Regards
D.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke679196cmvjgji6026p83lmm5vesvck8k@.
4ax.com...
> On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>
> Hi D,
> I'm fine, thanks!
> (BTW, sorry to have kept you waiting - I needed some time for this, and
> real life kept interfering).
>
> Thanks. I didn't recognise it from your earlier post, but now I do
> recall having that discussion with you (though I don't recall what
> exactly your original question was - but I think that's not really
> relevant to this question, right?)
>
> I have to disagree - in a proper table, there ALWAYS are keys. They may
> not be defined, and therefor unknown to the server, but LOGICALLY, the
> keys exist.
> Knowing the keys (the LOGICAL keys, that is) is important when querying
> a table. Keys tell you what columns to use in filtering, joining, or
> grouping. They tell you when to expect duplicates. Etc.
> For the record - you can add keys to a table that is populated with
> SELECT INTO. Whether or not you do that has no consequence for the logic
> of the query. It will change the performance of all queries and
> statements involving the table. It can also change the way unexpected
> bad data is handled.
> (snip)
> I must admit that I had some trouble understanding what you did. I'm
> still unsure if I understand your query completely. I did manage to
> write two alternatives that both produce the same results from the
> sample data you provided, but do test it against some other test sets as
> well, as I might have misinterpreted the logic of your query.
> -- Alternative #1
> SELECT Total AS [Total Combination],
> First AS [First Number],
> Second AS [Second Number],
> Third AS [Third Number],
> Fourth AS [Fourth Number],
> Fifth AS [Fifth Number]
> FROM (SELECT (SELECT COUNT(*)
> FROM Results AS b
> WHERE b.CombinationId = a.CombinationId) Total,
> SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
> 0 END) AS First,
> SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
> 0 END) AS Second,
> SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
> 0 END) AS Third,
> SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
> 0 END) AS Fourth,
> SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
> 0 END) AS Fifth
> FROM Combinations a
> WHERE Combination = 2
> GROUP BY a.CombinationId) AS x
> WHERE Total > 1
> ORDER BY Total DESC
> -- Alternative #2
> SELECT MAX(b.Total) AS [Total Combination],
> SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
> END) AS [First Number],
> SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
> END) AS [Second Number],
> SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
> END) AS [Third Number],
> SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
> END) AS [Fourth Number],
> SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
> END) AS [Fifth Number]
> FROM Combinations a
> INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
> FROM Results
> GROUP BY CombinationId
> HAVING COUNT(*) > 1) AS b
> ON b.CombinationId = a.CombinationId
> WHERE a.Combination = 2
> GROUP BY a.CombinationId
> ORDER BY SUM(b.Total) DESC
> go
>
> If my queries are correct, the next step is to do some performance
> tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
> get a quick overview. But the best way to test is to use an "empty"
> server (ie no other processes running), clear the cache, then measure
> the time taken to run one of the queries. Like this:
> CHECKPOINT -- Write dirty pages to disk
> DBCC FREEPROCCACHE -- Free procedure cache
> DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
> go
> DECLARE @.strt datetime, @.end datetime
> SET @.start = CURRENT_TIMESTAMP
> -- Insert query to be tested here.
> -- Use SELECT .. INTO #Tmp if result set is large,
> -- to eliminate network speed from the test
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.strt AS started, @.end AS ended,
> DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Can I use Top here and I'm stuck on how to get the results I'm looking for

The tables and queries are below with comments on what I'm trying to
accomplish. Thanks to all !
Copying from the next line to the end and pasting into QA does it all
(except what I'm looking for)
set nocount on
--drop table combinations
--drop table results
create table combinations (id int, combination int, innerindex int, value
int)
-- the combination field indicates a 2 number combination
-- although this just a sampling of my combinations table,
-- it holds 3,4 and 5 number combinations as well
-- innerindex is for my testing and is used below
-- value is the actual number from the 2 number combination
insert into combinations values(1,2,1,1)
insert into combinations values(1,2,2,2)
insert into combinations values(2,2,1,1)
insert into combinations values(2,2,2,3)
insert into combinations values(3,2,1,1)
insert into combinations values(3,2,2,4)
insert into combinations values(4,2,1,1)
insert into combinations values(4,2,2,5)
insert into combinations values(5,2,1,2)
insert into combinations values(5,2,2,3)
insert into combinations values(6,2,1,2)
insert into combinations values(6,2,2,4)
go
create table results (id int, testsetid int)
-- the results table lists which testsets (from another table)
-- have which combination. So for the first record
-- testset 50 contains the combination 1,2 as indicated
-- by the id of 1
insert into results values(5,50)
insert into results values(1,2)
insert into results values(6,50)
insert into results values(1,2)
insert into results values(4,450)
insert into results values(1,2)
insert into results values(3,150)
insert into results values(1,2)
go
-- this works nicely to give me a total of the results grouped by the
combinations
select id, count(*) as Total from results group by id order by count(*) desc
-- Is there a way to use the Top function to only
-- give me the top 1 or 5 or 5 percent
select a.id, count(*) as Total from results a group by a.id
having a.id in (select id from combinations where combination = 2)
order by total desc
-- here I am trying to get a Total and the actual combination listed
horizontally
-- but I don't see how. I was reading that this type of thing should be done
in the
-- presentation layer but I'm not sure how I can do it there either.
-- I'm using c# and can relate the tables via a dataset but I;m still stuck
-- any suggestions would be appreciated.
select a.id, count(*) as Total,
SUM(CASE [innerindex] WHEN 1 THEN value ELSE ' ' END)AS F1,
SUM(CASE [innerindex] WHEN 2 THEN value ELSE ' ' END)AS F2,
SUM(CASE [innerindex] WHEN 3 THEN value ELSE ' ' END)AS F3,
SUM(CASE [innerindex] WHEN 4 THEN value ELSE ' ' END)AS F4,
SUM(CASE [innerindex] WHEN 5 THEN value ELSE ' ' END)AS F5
from combinations a join results b on a.id = b.id
group by a.id
order by a.id
-- the ultimate goal is to have something that looks like
-- Total Combination
-- 55 1,3,8,55
-- 10 1,3
-- 8 1,9
-- and if I could say show me the top 3 number combinations or
-- top 5 percent of all combinations etc etc
-- thanks alot!!!!
drop table combinations
drop table results
On Tue, 17 May 2005 11:03:10 -0400, D wrote:

>The tables and queries are below with comments on what I'm trying to
>accomplish. Thanks to all !
>Copying from the next line to the end and pasting into QA does it all
>(except what I'm looking for)
(snip)
Hi D,
First: thanks for posting the CREATE TABLE and INSERT statements and the
requested output.
Unfortunately, it didn't help in this case - I am still at a loss when I
try to grok what you're trying to do.
It might help if your script included the constraints as well. The
tables you posted have no primary key, and there is no foreign key to
indicate the relationship either.
For a better understanding, a short explanation of the business problem
you're trying to solve might help lots too.
Finally, I really don't see how the required rersults you posted relate
to the sample data in the INSERT statements. Could you elaborate on
that?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hey Hugo hows it going?
This code was actually based upon some of your examples that you showed me
in an earlier post I had. At the time I was trying to search for
combinations within a table and you had suggested I create a normalized
table of combinations and join in the data table to seek out the
combinations. I have attached it at the very end of this message in case you
wish to review, look for 'Start of old post' below.
I created the table of combinations (called combinations) and I have my
table of data then I was running the join that you gave me but I was
concerned that it was taking a long time to complete. I then changed the sql
select to 'SELECT w.combination, t.testset into results' to save the results
except I renamed combination to id and testset to testsetid.
So in the 'Query Analyzer Section' below, the table 'Results' is the actual
output from the join query in the section 'Start of old post' and there
isn't any keys set because I was just doing a select into
(I changed some names and text to make it more clear)
============== Start of Query Analyzer Section
====================================
set nocount on
--drop table Combinations
--drop table Results
create table Combinations
(CombinationId int, Combination int, InnerIndex int, CombinationValue int,
primary key(CombinationId, Combination, InnerIndex) )
-- CombinationId = Identity field
-- Combination = field indicates a 2 number combination
-- although this just a sampling of my Combinations table,
-- it holds 3,4 and 5 number Combinations as well
-- InnerIndex = for my testing and is used below
-- CombinationValue = is a number from the 2 number combination
insert into Combinations values(1,2,1,1)
insert into Combinations values(1,2,2,2)
insert into Combinations values(2,2,1,1)
insert into Combinations values(2,2,2,3)
insert into Combinations values(3,2,1,1)
insert into Combinations values(3,2,2,4)
insert into Combinations values(4,2,1,1)
insert into Combinations values(4,2,2,5)
insert into Combinations values(5,2,1,2)
insert into Combinations values(5,2,2,3)
insert into Combinations values(6,2,1,2)
insert into Combinations values(6,2,2,4)
go
create table Results (CombinationId int, TestsetId int)
-- The Results table lists the CombinationId's
-- which you could consider a foreign key
-- that originates from the Combinations table
-- and the Testsetid
-- which you could consider a foreign key
-- that originates from my data table not shown
-- For the records below
-- Testsetid 1 contains the combination 1,2 as indicated
-- by the Combinationid of 1
insert into Results values(1,1)
-- Testsetid 50 contains the combination 2,4 as indicated
-- by the Combinationid of 6
insert into Results values(5,50)
insert into Results values(6,50)
insert into Results values(1,2)
insert into Results values(4,450)
insert into Results values(1,3)
insert into Results values(3,150)
insert into Results values(1,4)
insert into Results values(2,1)
insert into Results values(2,2)
insert into Results values(2,3)
insert into Results values(2,4)
insert into Results values(2,5)
insert into Results values(2,6)
go
-- here I found the solution that I was looking for
-- **see comments in post about this
-- the top row of the Results is
-- 6 1 3 0 0 0
-- which reads there are 6 Combinations of 1 & 3
-- the next row reads there are 4 Combinations of 1 & 2
select
(
select count(*) from Results b where a.CombinationId = b.CombinationId
group by b.CombinationId
) [Total Combinations],
SUM(CASE [InnerIndex] WHEN 1 THEN CombinationValue ELSE ' ' END)AS [First
Number],
SUM(CASE [InnerIndex] WHEN 2 THEN CombinationValue ELSE ' ' END)AS [Second
Number],
SUM(CASE [InnerIndex] WHEN 3 THEN CombinationValue ELSE ' ' END)AS [Third
Number],
SUM(CASE [InnerIndex] WHEN 4 THEN CombinationValue ELSE ' ' END)AS [Fourth
Number],
SUM(CASE [InnerIndex] WHEN 5 THEN CombinationValue ELSE ' ' END)AS [Fifth
Number]
from Combinations a
group by a.CombinationId
having a.CombinationId in
(
-- using this I can select the combination I want
-- this says only show me the Results from the 2 number Combinations
select a.CombinationId from Results a
group by a.CombinationId
having a.CombinationId in (select CombinationId from Combinations where
Combination = 2)
-- using this count I can control some of the Results
and count(*) > 1
)
order by [Total Combinations] desc
-- thanks alot!!!!
drop table Combinations
drop table Results
============== End of Query Analyzer Section
====================================
*** Comments about my large query
It works good for small sets like these but if I set it to search from a 4
or 5 number combinations from my results table
which contains 37960 records, it just slowly chugs away for a long long
time.
I was hoping to just get back the top 5 or 10 combinations and their totals
but sql has to run every combination to figure out
the top totals.
Thanks for your help as always!!!!
============== Start of old post ====================================
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
============== End of old post ====================================
|||On Thu, 19 May 2005 11:15:42 -0400, D wrote:

>Hey Hugo hows it going?
Hi D,
I'm fine, thanks!
(BTW, sorry to have kept you waiting - I needed some time for this, and
real life kept interfering).

>This code was actually based upon some of your examples that you showed me
>in an earlier post I had. At the time I was trying to search for
>combinations within a table and you had suggested I create a normalized
>table of combinations and join in the data table to seek out the
>combinations. I have attached it at the very end of this message in case you
>wish to review, look for 'Start of old post' below.
Thanks. I didn't recognise it from your earlier post, but now I do
recall having that discussion with you (though I don't recall what
exactly your original question was - but I think that's not really
relevant to this question, right?)

>I created the table of combinations (called combinations) and I have my
>table of data then I was running the join that you gave me but I was
>concerned that it was taking a long time to complete. I then changed the sql
>select to 'SELECT w.combination, t.testset into results' to save the results
>except I renamed combination to id and testset to testsetid.
>So in the 'Query Analyzer Section' below, the table 'Results' is the actual
>output from the join query in the section 'Start of old post' and there
>isn't any keys set because I was just doing a select into
I have to disagree - in a proper table, there ALWAYS are keys. They may
not be defined, and therefor unknown to the server, but LOGICALLY, the
keys exist.
Knowing the keys (the LOGICAL keys, that is) is important when querying
a table. Keys tell you what columns to use in filtering, joining, or
grouping. They tell you when to expect duplicates. Etc.
For the record - you can add keys to a table that is populated with
SELECT INTO. Whether or not you do that has no consequence for the logic
of the query. It will change the performance of all queries and
statements involving the table. It can also change the way unexpected
bad data is handled.
(snip)
>*** Comments about my large query
>It works good for small sets like these but if I set it to search from a 4
>or 5 number combinations from my results table
>which contains 37960 records, it just slowly chugs away for a long long
>time.
>I was hoping to just get back the top 5 or 10 combinations and their totals
>but sql has to run every combination to figure out
>the top totals.
>Thanks for your help as always!!!!
I must admit that I had some trouble understanding what you did. I'm
still unsure if I understand your query completely. I did manage to
write two alternatives that both produce the same results from the
sample data you provided, but do test it against some other test sets as
well, as I might have misinterpreted the logic of your query.
-- Alternative #1
SELECT Total AS [Total Combination],
First AS [First Number],
Second AS [Second Number],
Third AS [Third Number],
Fourth AS [Fourth Number],
Fifth AS [Fifth Number]
FROM (SELECT (SELECT COUNT(*)
FROM Results AS b
WHERE b.CombinationId = a.CombinationId) Total,
SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
0 END) AS First,
SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
0 END) AS Second,
SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
0 END) AS Third,
SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
0 END) AS Fourth,
SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
0 END) AS Fifth
FROM Combinations a
WHERE Combination = 2
GROUP BY a.CombinationId) AS x
WHERE Total > 1
ORDER BY Total DESC
-- Alternative #2
SELECT MAX(b.Total) AS [Total Combination],
SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
END) AS [First Number],
SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
END) AS [Second Number],
SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
END) AS [Third Number],
SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
END) AS [Fourth Number],
SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
END) AS [Fifth Number]
FROM Combinations a
INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
FROM Results
GROUP BY CombinationId
HAVING COUNT(*) > 1) AS b
ON b.CombinationId = a.CombinationId
WHERE a.Combination = 2
GROUP BY a.CombinationId
ORDER BY SUM(b.Total) DESC
go
If my queries are correct, the next step is to do some performance
tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
get a quick overview. But the best way to test is to use an "empty"
server (ie no other processes running), clear the cache, then measure
the time taken to run one of the queries. Like this:
CHECKPOINT-- Write dirty pages to disk
DBCC FREEPROCCACHE-- Free procedure cache
DBCC DROPCLEANBUFFERS-- Flush non-dirty pages from cache
go
DECLARE @.strt datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert query to be tested here.
-- Use SELECT .. INTO #Tmp if result set is large,
-- to eliminate network speed from the test
SET @.end = CURRENT_TIMESTAMP
SELECT @.strt AS started, @.end AS ended,
DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I can't thank you enough for your help.
I haven't fully tested the results but they look good initially. Two quick
tests showed that alternative #2 was under a second and #1 took roughly 30
seconds. I'll have more comments and perhaps some questions later but I just
wanted to stop in here and thank you again because those are both brilliant
solutions and have expanded my sql knowledge enormously.
Best Regards
D.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke679196cmvjgji6026p83lmm5vesvck8k@.4ax.com...
> On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>
> Hi D,
> I'm fine, thanks!
> (BTW, sorry to have kept you waiting - I needed some time for this, and
> real life kept interfering).
>
> Thanks. I didn't recognise it from your earlier post, but now I do
> recall having that discussion with you (though I don't recall what
> exactly your original question was - but I think that's not really
> relevant to this question, right?)
>
> I have to disagree - in a proper table, there ALWAYS are keys. They may
> not be defined, and therefor unknown to the server, but LOGICALLY, the
> keys exist.
> Knowing the keys (the LOGICAL keys, that is) is important when querying
> a table. Keys tell you what columns to use in filtering, joining, or
> grouping. They tell you when to expect duplicates. Etc.
> For the record - you can add keys to a table that is populated with
> SELECT INTO. Whether or not you do that has no consequence for the logic
> of the query. It will change the performance of all queries and
> statements involving the table. It can also change the way unexpected
> bad data is handled.
> (snip)
> I must admit that I had some trouble understanding what you did. I'm
> still unsure if I understand your query completely. I did manage to
> write two alternatives that both produce the same results from the
> sample data you provided, but do test it against some other test sets as
> well, as I might have misinterpreted the logic of your query.
> -- Alternative #1
> SELECT Total AS [Total Combination],
> First AS [First Number],
> Second AS [Second Number],
> Third AS [Third Number],
> Fourth AS [Fourth Number],
> Fifth AS [Fifth Number]
> FROM (SELECT (SELECT COUNT(*)
> FROM Results AS b
> WHERE b.CombinationId = a.CombinationId) Total,
> SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
> 0 END) AS First,
> SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
> 0 END) AS Second,
> SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
> 0 END) AS Third,
> SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
> 0 END) AS Fourth,
> SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
> 0 END) AS Fifth
> FROM Combinations a
> WHERE Combination = 2
> GROUP BY a.CombinationId) AS x
> WHERE Total > 1
> ORDER BY Total DESC
> -- Alternative #2
> SELECT MAX(b.Total) AS [Total Combination],
> SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
> END) AS [First Number],
> SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
> END) AS [Second Number],
> SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
> END) AS [Third Number],
> SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
> END) AS [Fourth Number],
> SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
> END) AS [Fifth Number]
> FROM Combinations a
> INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
> FROM Results
> GROUP BY CombinationId
> HAVING COUNT(*) > 1) AS b
> ON b.CombinationId = a.CombinationId
> WHERE a.Combination = 2
> GROUP BY a.CombinationId
> ORDER BY SUM(b.Total) DESC
> go
>
> If my queries are correct, the next step is to do some performance
> tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
> get a quick overview. But the best way to test is to use an "empty"
> server (ie no other processes running), clear the cache, then measure
> the time taken to run one of the queries. Like this:
> CHECKPOINT -- Write dirty pages to disk
> DBCC FREEPROCCACHE -- Free procedure cache
> DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
> go
> DECLARE @.strt datetime, @.end datetime
> SET @.start = CURRENT_TIMESTAMP
> -- Insert query to be tested here.
> -- Use SELECT .. INTO #Tmp if result set is large,
> -- to eliminate network speed from the test
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.strt AS started, @.end AS ended,
> DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Can I use Top here and I'm stuck on how to get the results I'm looking for

The tables and queries are below with comments on what I'm trying to
accomplish. Thanks to all !
Copying from the next line to the end and pasting into QA does it all
(except what I'm looking for)
set nocount on
--drop table combinations
--drop table results
create table combinations (id int, combination int, innerindex int, value
int)
-- the combination field indicates a 2 number combination
-- although this just a sampling of my combinations table,
-- it holds 3,4 and 5 number combinations as well
-- innerindex is for my testing and is used below
-- value is the actual number from the 2 number combination
insert into combinations values(1,2,1,1)
insert into combinations values(1,2,2,2)
insert into combinations values(2,2,1,1)
insert into combinations values(2,2,2,3)
insert into combinations values(3,2,1,1)
insert into combinations values(3,2,2,4)
insert into combinations values(4,2,1,1)
insert into combinations values(4,2,2,5)
insert into combinations values(5,2,1,2)
insert into combinations values(5,2,2,3)
insert into combinations values(6,2,1,2)
insert into combinations values(6,2,2,4)
go
create table results (id int, testsetid int)
-- the results table lists which testsets (from another table)
-- have which combination. So for the first record
-- testset 50 contains the combination 1,2 as indicated
-- by the id of 1
insert into results values(5,50)
insert into results values(1,2)
insert into results values(6,50)
insert into results values(1,2)
insert into results values(4,450)
insert into results values(1,2)
insert into results values(3,150)
insert into results values(1,2)
go
-- this works nicely to give me a total of the results grouped by the
combinations
select id, count(*) as Total from results group by id order by count(*) desc
-- Is there a way to use the Top function to only
-- give me the top 1 or 5 or 5 percent
select a.id, count(*) as Total from results a group by a.id
having a.id in (select id from combinations where combination = 2)
order by total desc
-- here I am trying to get a Total and the actual combination listed
horizontally
-- but I don't see how. I was reading that this type of thing should be done
in the
-- presentation layer but I'm not sure how I can do it there either.
-- I'm using c# and can relate the tables via a dataset but I;m still stuck
-- any suggestions would be appreciated.
select a.id, count(*) as Total,
SUM(CASE [innerindex] WHEN 1 THEN value ELSE ' ' END)AS F1,
SUM(CASE [innerindex] WHEN 2 THEN value ELSE ' ' END)AS F2,
SUM(CASE [innerindex] WHEN 3 THEN value ELSE ' ' END)AS F3,
SUM(CASE [innerindex] WHEN 4 THEN value ELSE ' ' END)AS F4,
SUM(CASE [innerindex] WHEN 5 THEN value ELSE ' ' END)AS F5
from combinations a join results b on a.id = b.id
group by a.id
order by a.id
-- the ultimate goal is to have something that looks like
--
-- Total Combination
-- 55 1,3,8,55
-- 10 1,3
-- 8 1,9
-- and if I could say show me the top 3 number combinations or
-- top 5 percent of all combinations etc etc
-- thanks alot!!!!
drop table combinations
drop table resultsOn Tue, 17 May 2005 11:03:10 -0400, D wrote:
>The tables and queries are below with comments on what I'm trying to
>accomplish. Thanks to all !
>Copying from the next line to the end and pasting into QA does it all
>(except what I'm looking for)
(snip)
Hi D,
First: thanks for posting the CREATE TABLE and INSERT statements and the
requested output.
Unfortunately, it didn't help in this case - I am still at a loss when I
try to grok what you're trying to do.
It might help if your script included the constraints as well. The
tables you posted have no primary key, and there is no foreign key to
indicate the relationship either.
For a better understanding, a short explanation of the business problem
you're trying to solve might help lots too.
Finally, I really don't see how the required rersults you posted relate
to the sample data in the INSERT statements. Could you elaborate on
that?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hey Hugo hows it going?
This code was actually based upon some of your examples that you showed me
in an earlier post I had. At the time I was trying to search for
combinations within a table and you had suggested I create a normalized
table of combinations and join in the data table to seek out the
combinations. I have attached it at the very end of this message in case you
wish to review, look for 'Start of old post' below.
I created the table of combinations (called combinations) and I have my
table of data then I was running the join that you gave me but I was
concerned that it was taking a long time to complete. I then changed the sql
select to 'SELECT w.combination, t.testset into results' to save the results
except I renamed combination to id and testset to testsetid.
So in the 'Query Analyzer Section' below, the table 'Results' is the actual
output from the join query in the section 'Start of old post' and there
isn't any keys set because I was just doing a select into
(I changed some names and text to make it more clear)
============== Start of Query Analyzer Section
====================================
set nocount on
--drop table Combinations
--drop table Results
create table Combinations
(CombinationId int, Combination int, InnerIndex int, CombinationValue int,
primary key(CombinationId, Combination, InnerIndex) )
-- CombinationId = Identity field
-- Combination = field indicates a 2 number combination
-- although this just a sampling of my Combinations table,
-- it holds 3,4 and 5 number Combinations as well
-- InnerIndex = for my testing and is used below
-- CombinationValue = is a number from the 2 number combination
insert into Combinations values(1,2,1,1)
insert into Combinations values(1,2,2,2)
insert into Combinations values(2,2,1,1)
insert into Combinations values(2,2,2,3)
insert into Combinations values(3,2,1,1)
insert into Combinations values(3,2,2,4)
insert into Combinations values(4,2,1,1)
insert into Combinations values(4,2,2,5)
insert into Combinations values(5,2,1,2)
insert into Combinations values(5,2,2,3)
insert into Combinations values(6,2,1,2)
insert into Combinations values(6,2,2,4)
go
create table Results (CombinationId int, TestsetId int)
-- The Results table lists the CombinationId's
-- which you could consider a foreign key
-- that originates from the Combinations table
-- and the Testsetid
-- which you could consider a foreign key
-- that originates from my data table not shown
-- For the records below
-- Testsetid 1 contains the combination 1,2 as indicated
-- by the Combinationid of 1
insert into Results values(1,1)
-- Testsetid 50 contains the combination 2,4 as indicated
-- by the Combinationid of 6
insert into Results values(5,50)
insert into Results values(6,50)
insert into Results values(1,2)
insert into Results values(4,450)
insert into Results values(1,3)
insert into Results values(3,150)
insert into Results values(1,4)
insert into Results values(2,1)
insert into Results values(2,2)
insert into Results values(2,3)
insert into Results values(2,4)
insert into Results values(2,5)
insert into Results values(2,6)
go
-- here I found the solution that I was looking for
-- **see comments in post about this
--
-- the top row of the Results is
-- 6 1 3 0 0 0
-- which reads there are 6 Combinations of 1 & 3
-- the next row reads there are 4 Combinations of 1 & 2
select
(
select count(*) from Results b where a.CombinationId = b.CombinationId
group by b.CombinationId
) [Total Combinations],
SUM(CASE [InnerIndex] WHEN 1 THEN CombinationValue ELSE ' ' END)AS [First
Number],
SUM(CASE [InnerIndex] WHEN 2 THEN CombinationValue ELSE ' ' END)AS [Second
Number],
SUM(CASE [InnerIndex] WHEN 3 THEN CombinationValue ELSE ' ' END)AS [Third
Number],
SUM(CASE [InnerIndex] WHEN 4 THEN CombinationValue ELSE ' ' END)AS [Fourth
Number],
SUM(CASE [InnerIndex] WHEN 5 THEN CombinationValue ELSE ' ' END)AS [Fifth
Number]
from Combinations a
group by a.CombinationId
having a.CombinationId in
(
-- using this I can select the combination I want
-- this says only show me the Results from the 2 number Combinations
select a.CombinationId from Results a
group by a.CombinationId
having a.CombinationId in (select CombinationId from Combinations where
Combination = 2)
-- using this count I can control some of the Results
and count(*) > 1
)
order by [Total Combinations] desc
-- thanks alot!!!!
drop table Combinations
drop table Results
============== End of Query Analyzer Section
====================================
*** Comments about my large query
It works good for small sets like these but if I set it to search from a 4
or 5 number combinations from my results table
which contains 37960 records, it just slowly chugs away for a long long
time.
I was hoping to just get back the top 5 or 10 combinations and their totals
but sql has to run every combination to figure out
the top totals.
Thanks for your help as always!!!!
============== Start of old post ====================================
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
============== End of old post ====================================|||On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>Hey Hugo hows it going?
Hi D,
I'm fine, thanks!
(BTW, sorry to have kept you waiting - I needed some time for this, and
real life kept interfering).
>This code was actually based upon some of your examples that you showed me
>in an earlier post I had. At the time I was trying to search for
>combinations within a table and you had suggested I create a normalized
>table of combinations and join in the data table to seek out the
>combinations. I have attached it at the very end of this message in case you
>wish to review, look for 'Start of old post' below.
Thanks. I didn't recognise it from your earlier post, but now I do
recall having that discussion with you (though I don't recall what
exactly your original question was - but I think that's not really
relevant to this question, right?)
>I created the table of combinations (called combinations) and I have my
>table of data then I was running the join that you gave me but I was
>concerned that it was taking a long time to complete. I then changed the sql
>select to 'SELECT w.combination, t.testset into results' to save the results
>except I renamed combination to id and testset to testsetid.
>So in the 'Query Analyzer Section' below, the table 'Results' is the actual
>output from the join query in the section 'Start of old post' and there
>isn't any keys set because I was just doing a select into
I have to disagree - in a proper table, there ALWAYS are keys. They may
not be defined, and therefor unknown to the server, but LOGICALLY, the
keys exist.
Knowing the keys (the LOGICAL keys, that is) is important when querying
a table. Keys tell you what columns to use in filtering, joining, or
grouping. They tell you when to expect duplicates. Etc.
For the record - you can add keys to a table that is populated with
SELECT INTO. Whether or not you do that has no consequence for the logic
of the query. It will change the performance of all queries and
statements involving the table. It can also change the way unexpected
bad data is handled.
(snip)
>*** Comments about my large query
>It works good for small sets like these but if I set it to search from a 4
>or 5 number combinations from my results table
>which contains 37960 records, it just slowly chugs away for a long long
>time.
>I was hoping to just get back the top 5 or 10 combinations and their totals
>but sql has to run every combination to figure out
>the top totals.
>Thanks for your help as always!!!!
I must admit that I had some trouble understanding what you did. I'm
still unsure if I understand your query completely. I did manage to
write two alternatives that both produce the same results from the
sample data you provided, but do test it against some other test sets as
well, as I might have misinterpreted the logic of your query.
-- Alternative #1
SELECT Total AS [Total Combination],
First AS [First Number],
Second AS [Second Number],
Third AS [Third Number],
Fourth AS [Fourth Number],
Fifth AS [Fifth Number]
FROM (SELECT (SELECT COUNT(*)
FROM Results AS b
WHERE b.CombinationId = a.CombinationId) Total,
SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
0 END) AS First,
SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
0 END) AS Second,
SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
0 END) AS Third,
SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
0 END) AS Fourth,
SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
0 END) AS Fifth
FROM Combinations a
WHERE Combination = 2
GROUP BY a.CombinationId) AS x
WHERE Total > 1
ORDER BY Total DESC
-- Alternative #2
SELECT MAX(b.Total) AS [Total Combination],
SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
END) AS [First Number],
SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
END) AS [Second Number],
SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
END) AS [Third Number],
SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
END) AS [Fourth Number],
SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
END) AS [Fifth Number]
FROM Combinations a
INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
FROM Results
GROUP BY CombinationId
HAVING COUNT(*) > 1) AS b
ON b.CombinationId = a.CombinationId
WHERE a.Combination = 2
GROUP BY a.CombinationId
ORDER BY SUM(b.Total) DESC
go
If my queries are correct, the next step is to do some performance
tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
get a quick overview. But the best way to test is to use an "empty"
server (ie no other processes running), clear the cache, then measure
the time taken to run one of the queries. Like this:
CHECKPOINT -- Write dirty pages to disk
DBCC FREEPROCCACHE -- Free procedure cache
DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
go
DECLARE @.strt datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert query to be tested here.
-- Use SELECT .. INTO #Tmp if result set is large,
-- to eliminate network speed from the test
SET @.end = CURRENT_TIMESTAMP
SELECT @.strt AS started, @.end AS ended,
DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I can't thank you enough for your help.
I haven't fully tested the results but they look good initially. Two quick
tests showed that alternative #2 was under a second and #1 took roughly 30
seconds. I'll have more comments and perhaps some questions later but I just
wanted to stop in here and thank you again because those are both brilliant
solutions and have expanded my sql knowledge enormously.
Best Regards
D.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke679196cmvjgji6026p83lmm5vesvck8k@.4ax.com...
> On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>>Hey Hugo hows it going?
> Hi D,
> I'm fine, thanks!
> (BTW, sorry to have kept you waiting - I needed some time for this, and
> real life kept interfering).
>
>>This code was actually based upon some of your examples that you showed me
>>in an earlier post I had. At the time I was trying to search for
>>combinations within a table and you had suggested I create a normalized
>>table of combinations and join in the data table to seek out the
>>combinations. I have attached it at the very end of this message in case
>>you
>>wish to review, look for 'Start of old post' below.
> Thanks. I didn't recognise it from your earlier post, but now I do
> recall having that discussion with you (though I don't recall what
> exactly your original question was - but I think that's not really
> relevant to this question, right?)
>
>>I created the table of combinations (called combinations) and I have my
>>table of data then I was running the join that you gave me but I was
>>concerned that it was taking a long time to complete. I then changed the
>>sql
>>select to 'SELECT w.combination, t.testset into results' to save the
>>results
>>except I renamed combination to id and testset to testsetid.
>>So in the 'Query Analyzer Section' below, the table 'Results' is the
>>actual
>>output from the join query in the section 'Start of old post' and there
>>isn't any keys set because I was just doing a select into
> I have to disagree - in a proper table, there ALWAYS are keys. They may
> not be defined, and therefor unknown to the server, but LOGICALLY, the
> keys exist.
> Knowing the keys (the LOGICAL keys, that is) is important when querying
> a table. Keys tell you what columns to use in filtering, joining, or
> grouping. They tell you when to expect duplicates. Etc.
> For the record - you can add keys to a table that is populated with
> SELECT INTO. Whether or not you do that has no consequence for the logic
> of the query. It will change the performance of all queries and
> statements involving the table. It can also change the way unexpected
> bad data is handled.
> (snip)
>>*** Comments about my large query
>>It works good for small sets like these but if I set it to search from a 4
>>or 5 number combinations from my results table
>>which contains 37960 records, it just slowly chugs away for a long long
>>time.
>>I was hoping to just get back the top 5 or 10 combinations and their
>>totals
>>but sql has to run every combination to figure out
>>the top totals.
>>Thanks for your help as always!!!!
> I must admit that I had some trouble understanding what you did. I'm
> still unsure if I understand your query completely. I did manage to
> write two alternatives that both produce the same results from the
> sample data you provided, but do test it against some other test sets as
> well, as I might have misinterpreted the logic of your query.
> -- Alternative #1
> SELECT Total AS [Total Combination],
> First AS [First Number],
> Second AS [Second Number],
> Third AS [Third Number],
> Fourth AS [Fourth Number],
> Fifth AS [Fifth Number]
> FROM (SELECT (SELECT COUNT(*)
> FROM Results AS b
> WHERE b.CombinationId = a.CombinationId) Total,
> SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
> 0 END) AS First,
> SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
> 0 END) AS Second,
> SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
> 0 END) AS Third,
> SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
> 0 END) AS Fourth,
> SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
> 0 END) AS Fifth
> FROM Combinations a
> WHERE Combination = 2
> GROUP BY a.CombinationId) AS x
> WHERE Total > 1
> ORDER BY Total DESC
> -- Alternative #2
> SELECT MAX(b.Total) AS [Total Combination],
> SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
> END) AS [First Number],
> SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
> END) AS [Second Number],
> SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
> END) AS [Third Number],
> SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
> END) AS [Fourth Number],
> SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
> END) AS [Fifth Number]
> FROM Combinations a
> INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
> FROM Results
> GROUP BY CombinationId
> HAVING COUNT(*) > 1) AS b
> ON b.CombinationId = a.CombinationId
> WHERE a.Combination = 2
> GROUP BY a.CombinationId
> ORDER BY SUM(b.Total) DESC
> go
>
> If my queries are correct, the next step is to do some performance
> tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
> get a quick overview. But the best way to test is to use an "empty"
> server (ie no other processes running), clear the cache, then measure
> the time taken to run one of the queries. Like this:
> CHECKPOINT -- Write dirty pages to disk
> DBCC FREEPROCCACHE -- Free procedure cache
> DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
> go
> DECLARE @.strt datetime, @.end datetime
> SET @.start = CURRENT_TIMESTAMP
> -- Insert query to be tested here.
> -- Use SELECT .. INTO #Tmp if result set is large,
> -- to eliminate network speed from the test
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.strt AS started, @.end AS ended,
> DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Can I use OUTER JOIN on 2 columns at the same time?

I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.

I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.

I have following query which does not produce desired result

SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.

I'm pretty sure it's simple solution but can not figure out myself.

The query is correct ,it has to give the expected result.

Could you provide us with sample data?

|||

That's true it works with scenario I specified but I simplified my real life challenge. Table2 actually contains an extra column called Game of type int. It's nullable. I want the same thing I specified originally but only if Game=15, so my tables are

Table1

label_id athlete_id

1 1 2 1 3 1 1 2 2 2 3 2

Table2

label_id athlete_id data game

1 2 5 15 2 3 4 15 1 7 7 17

I want all rows from Table1 corresponding to label_id and athlete_id between tables for Game=15

Query below (

SELECT Table1.label_id, Table1.athlete_id, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

where game=15

)

returns single row. How can I return all rows with null values where no matches are found and only for game_id

|||

What is your expected result?

I think that following is the expected results from the records you given

labelid athleteid data game

1 1 NULL NULL

2 1 NULL NULL

3 1 NULL NULL

1 2 5 15

2 2 NULL NULL

3 2 NULL NULL

The following is the query

Code Snippet

Select * from

(SELECT Table1.labelid, Table1.atheleteid, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.labelid = Table2.labelid AND Table1.atheleteid = Table2.atheleteid)) as TempTable

where Game=15 or Game is null

|||

Use Full Outer Join:

Code Snippet

SELECT

isnull(table1.label_id,table2.label_id),

isnull(table1.athlete_id,table2.athlete_id),

data,

game

FROM

table1

FULL OUTER JOIN table2 ON

(

table1.label_id = table2.label_id

and table1.athlete_id = table2.athlete_id

)

WHERE

game=15

/*

label_idathlete_iddatagame

-- -- -- --

12515

23415

*/

Move the Where clause to ON itself

Code Snippet

SELECT

Table1.label_id,

Table1.athlete_id,

data,

game

FROM

Table1 Table1

LEFT OUTER JOIN Table2 table2 ON

(

Table1.label_id = Table2.label_id

AND Table1.athlete_id = Table2.athlete_id

) and game=15

/*

label_idathlete_iddatagame

-- -- -- --

11NULLNULL

21NULLNULL

31NULLNULL

12515

22NULLNULL

32NULLNULL

*/