A Challenging Query

Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:

>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.

>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

64-bit SQL

I have run into an interesting problem. I have some code that adds a column
to an existing table. The column is set as primary key, identity and
clustered. On the 32-bit version of SQL Server 2005 it works fine. It
fails on the 64-bit version of SQL Server 2005 intermittently with a "could
not create unique index because duplicate values were found". Kind of odd,
considering as an identity field it's creating the values. I was able to
recreate the problem with the following schema:
Create table test1
(col1 varchar(20),
col2 varchar(20),
col3 uniqueidentifier default newid())
-- insert data
Declare @.counter int
set @.counter=1
While @.counter < 1000000
BEGIN
insert into test1
values ('Joe','Smith',default)
Set @.counter=@.counter+1
END
-- add column
Alter table test1 add col4 int constraint PK_test1 primary key clustered
identity
After Running this, I get this error:
CREATE UNIQUE INDEX terminated because a duplicate key was found for object
name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28).
Anybody else run into this? Why would this be happening, and is there any
way to fix it? I'm running the 64-bit version of Windows 2003 and the the
64-bit version of SQL Server 2005.
Thanks in advance,
MarkI don't have 64-bit system handy to test this on but you might see if you
can recreate the problem with parallelism off.
ALTER TABLE test1 ADD col4 int
CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
WITH (MAXDOP = 1)
IDENTITY
Hope this helps.
Dan Guzman
SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:efXagtwBGHA.2356@.tk2msftngp13.phx.gbl...
>I have run into an interesting problem. I have some code that adds a
>column to an existing table. The column is set as primary key, identity
>and clustered. On the 32-bit version of SQL Server 2005 it works fine. It
>fails on the 64-bit version of SQL Server 2005 intermittently with a "could
>not create unique index because duplicate values were found". Kind of odd,
>considering as an identity field it's creating the values. I was able to
>recreate the problem with the following schema:
> Create table test1
> (col1 varchar(20),
> col2 varchar(20),
> col3 uniqueidentifier default newid())
> -- insert data
> Declare @.counter int
> set @.counter=1
> While @.counter < 1000000
> BEGIN
> insert into test1
> values ('Joe','Smith',default)
> Set @.counter=@.counter+1
> END
> -- add column
> Alter table test1 add col4 int constraint PK_test1 primary key clustered
> identity
> After Running this, I get this error:
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value
> is (28).
> Anybody else run into this? Why would this be happening, and is there any
> way to fix it? I'm running the 64-bit version of Windows 2003 and the the
> 64-bit version of SQL Server 2005.
> Thanks in advance,
> Mark
>
>|||I appreciate the help Dan. I had the same thought, (that it may be a
parellelism problem), but I hadn't tried your syntax. Unfortunately, I got
the same error after using your suggestion.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>I don't have 64-bit system handy to test this on but you might see if you
>can recreate the problem with parallelism off.
> ALTER TABLE test1 ADD col4 int
> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
> WITH (MAXDOP = 1)
> IDENTITY
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||The problem is intermittent. The first run of your syntax failed, the
second succeeded, the third failed.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>I don't have 64-bit system handy to test this on but you might see if you
>can recreate the problem with parallelism off.
> ALTER TABLE test1 ADD col4 int
> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
> WITH (MAXDOP = 1)
> IDENTITY
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:efXagtwBGHA.2356@.tk2msftngp13.phx.gbl...
>|||Even though the MAXDOP 1 didn't work around the problem, this information is
useful. I suggest you report this apparent bug using the feedback center at
http://lab.msdn.microsoft.com/productfeedback/. Include your repro script
and be sure to specify that the problem is intermittent, occurs only on
64-bit and that the MAXDOP 1 hint didn't help.
If you need a workaround before the problem is resolved, you might try
adding the identity column and primary key as separate statements.
Hope this helps.
Dan Guzman
SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:eAlN3MxBGHA.984@.tk2msftngp13.phx.gbl...
> The problem is intermittent. The first run of your syntax failed, the
> second succeeded, the third failed.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Dan,
I'll report the bug. By the way, I replaced the alter table component with
the following code:
Alter table test1 add col4 int identity
go
Alter table test1 add constraint pk_test1 primary key(col4)
And I still get the same error message.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oz98RYxBGHA.1092@.TK2MSFTNGP09.phx.gbl...

> Even though the MAXDOP 1 didn't work around the problem, this information
> is useful. I suggest you report this apparent bug using the feedback
> center at http://lab.msdn.microsoft.com/productfeedback/. Include your
> repro script and be sure to specify that the problem is intermittent,
> occurs only on 64-bit and that the MAXDOP 1 hint didn't help.
> If you need a workaround before the problem is resolved, you might try
> adding the identity column and primary key as separate statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:eAlN3MxBGHA.984@.tk2msftngp13.phx.gbl...
>