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)

80 mode

Hello everybody.
What mean if server is runninf in the default 80 mode
What are 60, 65,70 mode.
Thanks for all respond."harry" <simon_add@.op.pl> wrote in message news:d1s8fk$rbv$1@.news.onet.pl...
> Hello everybody.
> What mean if server is runninf in the default 80 mode
> What are 60, 65,70 mode.
>
They are the versions of SQL Server - 6.0, 6.5, 7.0 and 8.0 (2000). The best
bet is to look up the differences in BOL, though the most significant
changes occurred between 7.0 and 8.0 as far as I know.
Yours,
Ann-Marie|||On Wed, 23 Mar 2005 18:19:57 +0100, harry wrote:

>Hello everybody.
>What mean if server is runninf in the default 80 mode
>What are 60, 65,70 mode.
>Thanks for all respond.
>
Hi Harry,
That's the compatibility mode.
60, 65, and 70 indicate that SQL Server takes some steps to pretend to
be a SQL Server 6.0, 6.5, or 7.0 installation, rather than SQL Server
2000. Some new commands are not available in the old compatibility
modes. Some fixed bugs will reappear in the old compatibility modes. And
some discontinued functions are still avaialable in the compatibility
modes.
Note that compatibility mode is a database setting - it doesn't affect
all databases on the server, just one - but it can't be switched on an
ad-hoc basis in case you need old behaviour AND new functionality.
Also note that the compatibility is limited. Many discuontinued or
changed commands, options or functionalities remain unavailable or
changed even when running at an older compatibility level.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)