November 21, 2011 at 11:48 am
Ninja's_RGR'us (11/21/2011)
IE, this works, slow as hell =>
use master ;
;
WITH CTE ( object_id, summ )
AS (
SELECT DISTINCT
object_id
, SUM(column_id) OVER ( PARTITION BY object_id ) AS summ
FROM
sys.all_columns
)
SELECT
object_id
, summ
FROM
CTE
WHERE
summ = (
SELECT
MAX(summ)
FROM
CTE
)
Thanks for the query. I will rubber this test in my professor face and show him that sql server 2008 is optimized for those querys you showed me before...
November 21, 2011 at 11:54 am
bomborde (11/21/2011)
I still want to learn on my own as always try to do. I searched for efficiency tests about the order by, max(), and so on. but nothing. Since you guys know more than me (and certainly you do) I tried my luck at this forum. And I am very thankful for the all answers you provide me. If I "discover" something else that could improve your knowledge I will bump this post for sure.ps: sorry my bad english...
Not a problem, so long as you're honest about it.
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 11:56 am
bomborde (11/21/2011)
. . . I must do this without a select inside a from.. . .
Can anyone do this without a 'view' or 'join' or 'order by'? . . .
Back to the original post. I have tried for about an hour but so far I have not been able to do it within these limitations.
Since GSquared and Ninja's had to use one of these constructs, I would conclude that it cannot be solved, and the objective of the task is to see the candidate deal with a difficult problem, under stress.
I am still trying, though... 😉
November 21, 2011 at 12:03 pm
Revenant (11/21/2011)
bomborde (11/21/2011)
. . . I must do this without a select inside a from.. . .
Can anyone do this without a 'view' or 'join' or 'order by'? . . .
Back to the original post. I have tried for about an hour but so far I have not been able to do it within these limitations.
Since GSquared and Ninja's had to use one of these constructs, I would conclude that it cannot be solved, and the objective of the task is to see the candidate deal with a difficult problem, under stress.
I am still trying, though... 😉
It can be done within those constraints. It's just insanely complex, horribly slow, and violates all kinds of "don't ever do that again" kind of rules. For example, running a cursor on a simple Sum() Group By would not involve a view, a join or an order by, but could get the answer. If you also have to avoid both distinct and group by, you run a cursor, use that to build the sum manually in a temp table, and then run a cursor on the temp table to find the top value.
It's a very strong case of "can be done" != "should be done".
Which is why I originally asked about homework/test/interview, because sometimes those don't care about "should", only about "can", in an effort to test ability to think outside the box or show understanding of specific tools in an academic/theoretical sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 12:22 pm
DECLARE @temp TABLE ( name VARCHAR(20), total INT );
INSERT INTO @temp
SELECT Name, SUM(Consumption)
FROM tablename
GROUP BY Name;
DECLARE @maxValue INT = ( SELECT MAX(total) FROM @temp );
SELECT name, total
FROM @temp
WHERE total = @maxValue;
This one works perfectly, but it is a cheat that satisfies the 'no subselect' condition by setting the value to a local variable.
November 21, 2011 at 12:43 pm
Revenant (11/21/2011)
DECLARE @temp TABLE ( name VARCHAR(20), total INT );
INSERT INTO @temp
SELECT Name, SUM(Consumption)
FROM tablename
GROUP BY Name;
DECLARE @maxValue INT = ( SELECT MAX(total) FROM @temp );
SELECT name, total
FROM @temp
WHERE total = @maxValue;
This one works perfectly, but it is a cheat that satisfies the 'no subselect' condition by setting the value to a local variable.
I was interpretting it a little more strictly than that, on the no-subquery rule.
Still not as efficient as the simple Top Order By version.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 12:46 pm
GSquared (11/21/2011)
Revenant (11/21/2011)
DECLARE @temp TABLE ( name VARCHAR(20), total INT );
INSERT INTO @temp
SELECT Name, SUM(Consumption)
FROM tablename
GROUP BY Name;
DECLARE @maxValue INT = ( SELECT MAX(total) FROM @temp );
SELECT name, total
FROM @temp
WHERE total = @maxValue;
This one works perfectly, but it is a cheat that satisfies the 'no subselect' condition by setting the value to a local variable.
I was interpretting it a little more strictly than that, on the no-subquery rule.
Still not as efficient as the simple Top Order By version.
Agreed, but ORDER BY is on the 'prohibited' list.
November 21, 2011 at 2:56 pm
Sorry to add a post that does nothing to answer your question or productively contribute to the problem you are trying to solve, but it is just too difficult to remain silent.
If putting arbitrary restrictions on how you accomplish a problem is a requirement for getting a scholarship then I would say "no, thank-you, but you can keep your money". It is obvious that the person who made these restrictions isn't interested in actually teaching but is more interested in trying to prove how intelligent he is.
Here's the deal - in the real world where academics mean absolutely squat, you deal with data in ways that are mostly insane. There are no perfect world solutions and you need to know how to get and manipulate data in any situation at least two different ways (so you can verify that the first way is correct). The pristine environments of academia-land are completely irrelevant in the real world and appears that the creator of this little question of yours hasn't seen a critical production environment in a really long time.
...
November 21, 2011 at 3:57 pm
Edward Mlynar (11/21/2011)
If putting arbitrary restrictions on how you accomplish a problem is a requirement for getting a scholarship then I would say "no, thank-you, but you can keep your money". It is obvious that the person who made these restrictions isn't interested in actually teaching but is more interested in trying to prove how intelligent he is.
Yet, if you want to use their 'free money', you jump through their hoops. Sometimes it's worth it, especially if you need it instead of find it simply very helpful.
The pristine environments of academia-land are completely irrelevant in the real world and appears that the creator of this little question of yours hasn't seen a critical production environment in a really long time.
Can't argue that in the slightest. You still need to pass their tests before you get to prove they mean crapola in the real world. It's Ivory Tower work. Doesn't matter in the end though, unless you're going to self-train, and then figure out how to prove it without the degree as documentation, that you've done the work and know it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 21, 2011 at 4:04 pm
If you want to get dirty with this =>
SELECT id, sum() into #tmp
create clustered index on sum
declare @max-2 int
or use the cursor here to get the max value
select @max-2 = sum from tmp with index(0) maxdop 1, tablelock, repeatable reads (whatever needs to be there with Jeff's QU code)
delete from tmp where sum = @max... output deleted.*
Doesn't get much dirtier than that!
November 21, 2011 at 8:55 pm
bomborde (11/21/2011)
An order will be inefficient because it will order the whole table. I dont want this. (imagine a table with 500k tuples). I just want the maximum.
Nope... not true. If you look at the execution plan, ORDER BY on aggregates come AFTER the aggregations are complete. The aggregates aren't recalculated just for the ORDER BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply