Re: Select question
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/21/04
- Next message: Tom Moreau: "Re: Group and Count on 2 related tables"
- Previous message: Tom Moreau: "Re: writing a query"
- In reply to: simon: "Re: Select question"
- Next in thread: simon: "Re: Select question"
- Reply: simon: "Re: Select question"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 21 Oct 2004 22:11:15 +0200
On Thu, 21 Oct 2004 16:01:47 +0200, simon wrote:
(snip)
>I hope you understand now what I want.
Hi Simon,
I'm not sure. Posting CREATE TABLEs, INSERTs for sample data and expected
output is really a much better way to explain what you want than any
narrative and made up examples. Judging by your e-mail address and posting
host, English is not your native language. It's not mine either, so you
should expect misunderstandings if you don't use the one language that we
do have in common: SQL.
Anyway, if I understand your need correctly, you're doing something that
you might want to do in the presentation layer isntead. If that option is
not available, read on.
>If that won't be derived table, it's simple:
>
>SELECT T1.orderId,T1.productId,T1.value,
>quantity1=(SELECT sum(value) FROM table WHERE orderID=T1.orderID),
>quantity2=(SELECT sum(value) FROM table WHERE orderID=T1.orderID AND
>T1.crossell=0)
>FROM table as T1
>
>but if table is derived you can't create the same query
Yes, you can. But you'd have to repeat the subquery that defines the
derived table several times. Ugly, I know. Plus, I have seen evidence that
seems to indicate that SQL Server's optimizer doesn't take advantage of
the fact that the three subqueries are exactly identical.
An alternative would be to define a view that holds the definition of the
derived table. You can then reference the view as often as you wish in the
query. That would make the query as clean as it can be, but you might
still find SQL Server's optimizer producing odd-looking plans.
Other alternatives would involve temp tables, or derived tables using
GROUP BY, GROUPING and nasty CASE expressions. They'll defefinitely be
lots uglier than just repeating the derived table, so I wouldn't even
think about going there if I were you.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Tom Moreau: "Re: Group and Count on 2 related tables"
- Previous message: Tom Moreau: "Re: writing a query"
- In reply to: simon: "Re: Select question"
- Next in thread: simon: "Re: Select question"
- Reply: simon: "Re: Select question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
