Re: Help needed with subqueries.

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 13:50:49 +0100

TOP 100 Percent with an ORDER BY clause is meaningless, and can hinder
performance. The set that is returned by the subquery is treated like a view
or a table, which means that it is thought of as being unordered. Only when
you use TOP ... ORDER BY to limit the number of rows returned by the
subquery does it have any meaning. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
news:%23dW1jmQXEHA.1036@TK2MSFTNGP10.phx.gbl...
> 1. It works if you specify an alias for the subquery.
> Right
> 2. TOP 100 percent is needed if the subquery contains an ORDER BY clause.
>     An ORDER BY clause in a subquery is meaningless, If you are not using
> TOP.
>
> -- 
> Roji. P. Thomas
> SQL Server Programmer
> "Strange Cat" <strange@cat.com> wrote in message
> news:gOTDc.542881$rM4.22678060@news4.tin.it...
> > [CUT]
> >
> > Thanx to everyone answering!
> >
> > I would have spent a month searching for the answer... my SQL skills are
> > limited (to be fair to myself...).
> >
> > It actually appears that:
> >
> > 1. It works if you specify an alias for the subquery.
> > 2. TOP 100 percent is needed if the subquery contains an ORDER BY
clause.
> >
> > Working sample:
> > SELECT * FROM (SELECT TOP 100 percent * FROM T26ComDipendenti LEFT JOIN
> > T28ComGruppiDip ON T26ComDipendenti.T26Gruppo =
T28ComGruppiDip.T28Codice
> > ORDER BY T26Cognome, T26Nome) AS Risultati WHERE (T26Codice='00000001')
> >
> > Thanx again to all of you!
> >
> > Have a nice day
> >
> > ASC
> >
> >
> >
>
>

Relevant Pages

  • Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)
    ... > Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL ... > subquery, but maybe there is a simple syntax error. ... I need the PK's in the cross tab query to ... > This WHERE has the same IN Clause as J1T4. ...
    (microsoft.public.access.queries)
  • Re: query wont run in access 97
    ... It returns a syntax error FROM Clause. ... A Jet subquery used in a FROM clause will always ... it is wrapped in brackets with an ending period ... or the Jet query parser will choke on them. ...
    (microsoft.public.access.queries)
  • Re: Help with derived table SQL statement in Access
    ... SELECT expression on the FROM clause, but that is just that, a table ... You can use three forms of syntax to create a subquery: ... You can also use table name aliases in a subquery to refer to tables ... than the average salary of all employees having the same job title. ...
    (microsoft.public.access.queries)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: subquery
    ... Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER ... SELECT clause, ... You can "correlate" the subquery back to the main ... on a field in the main query. ...
    (microsoft.public.access.queries)