November 21, 2011 at 10:37 am
Ninja's_RGR'us (11/21/2011)
bomborde (11/21/2011)
Revenant (11/21/2011)
ScottPletcher (11/21/2011)
Don't see how you can do this w/o an ORDER BY, but it's trivial with one:
SELECT TOP 1 Name, SUM(Consumption) AS Total_Consumption
FROM tablename
GROUP BY Name
ORDER BY SUM(Consumption) DESC
This does not work if two or more people have the same total.
This is one of the problems I am facing...
This is not an issue. You need to figure out what is the tie breaker the plug it in. Nothing hard in there.
Or - look up what option you might use when there ARE ties.
As to the previous comment about ordering - the ordering is done on the aggregate subset, and you're not dealing with a raw data set and no other capabilities. Based on the little test I did (with 10M values), both solutions I tried return within .5s, and the order by is more efficient. I'd post my options, but since this is for an interview - I REALLY think (OP) should do the work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 21, 2011 at 10:41 am
ScottPletcher (11/21/2011)
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total, is it required to report both or can one arbitrarily be chosen?
I have to show both or more
November 21, 2011 at 10:44 am
Matt Miller (#4) (11/21/2011)
Ninja's_RGR'us (11/21/2011)
bomborde (11/21/2011)
Revenant (11/21/2011)
ScottPletcher (11/21/2011)
Don't see how you can do this w/o an ORDER BY, but it's trivial with one:
SELECT TOP 1 Name, SUM(Consumption) AS Total_Consumption
FROM tablename
GROUP BY Name
ORDER BY SUM(Consumption) DESC
This does not work if two or more people have the same total.
This is one of the problems I am facing...
This is not an issue. You need to figure out what is the tie breaker the plug it in. Nothing hard in there.
Or - look up what option you might use when there ARE ties.
As to the previous comment about ordering - the ordering is done on the aggregate subset, and you're not dealing with a raw data set and no other capabilities. Based on the little test I did (with 10M values), both solutions I tried return within .5s, and the order by is more efficient. I'd post my options, but since this is for an interview - I REALLY think (OP) should do the work.
thanks for the test. The interview is for a juniors scholarship and I am supposed to get there with the most efficient query and a select within a from is forbidden (since it will cause a view)
November 21, 2011 at 10:44 am
bomborde (11/21/2011)
ScottPletcher (11/21/2011)
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total, is it required to report both or can one arbitrarily be chosen?
I have to show both or more
Search in books online for the top operator and the "with ties" option.
November 21, 2011 at 10:45 am
bomborde (11/21/2011)
thanks for the test. The interview is for a juniors scholarship and I am supposed to get there with the most efficient query and a select within a from is forbidden (since it will cause a view)
It doesn't causes a view and it wouldn't necessarily make it slow (tho it could).
November 21, 2011 at 10:58 am
bomborde (11/21/2011)
a select within a from is forbidden (since it will cause a view)
Huh? It won't cause a view (that requires a CREATE VIEW statement) and several of the ways of doing this efficiently with the ties showing uses a derived table (a select within the FROM). Derived tables are very powerful and are not performance problems used properly.
But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 10:59 am
GilaMonster (11/21/2011)
But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?
We only want 33.33%, each :hehe:.
November 21, 2011 at 11:00 am
Ninja's_RGR'us (11/21/2011)
bomborde (11/21/2011)
ScottPletcher (11/21/2011)
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total, is it required to report both or can one arbitrarily be chosen?
I have to show both or more
Search in books online for the top operator and the "with ties" option.
WITH TIES is valid only with ORDER BY, and that the original post does not allow.
November 21, 2011 at 11:03 am
Revenant (11/21/2011)
Ninja's_RGR'us (11/21/2011)
bomborde (11/21/2011)
ScottPletcher (11/21/2011)
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total, is it required to report both or can one arbitrarily be chosen?
I have to show both or more
Search in books online for the top operator and the "with ties" option.
WITH TIES is valid only with ORDER BY, and that the original post does not allow.
Missed that part about the order by.
Since I don't really want to give it away nor waste time on the wrong way of doing things I'll shut up now. 😀
November 21, 2011 at 11:25 am
Ninja's_RGR'us (11/21/2011)
GilaMonster (11/21/2011)
But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?We only want 33.33%, each :hehe:.
GilaMonster (11/21/2011)
bomborde (11/21/2011)
a select within a from is forbidden (since it will cause a view)But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?
I will certainly pay you a beer or two. Take that as granted! 😀
November 21, 2011 at 11:26 am
bomborde (11/21/2011)
Ninja's_RGR'us (11/21/2011)
GilaMonster (11/21/2011)
But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?We only want 33.33%, each :hehe:.
GilaMonster (11/21/2011)
bomborde (11/21/2011)
a select within a from is forbidden (since it will cause a view)But, since this is a kind of test (for a scholarship), you should really be doing this yourself, or will you share the prize with us if you win?
I will certainly pay you a beer or two. Take that as granted! 😀
Sorry I don't drink.
And 33% is for lifetime.
Still want a "free" giveaway or want to learn on your own??
November 21, 2011 at 11:29 am
I don't drink beer and I'd be very hard-pressed to do this efficiently without an order by or a derived table.
If you must, first figure out how to get the max of a sum. Then figure out how to filter for the rows where the sum = that max of sum. It still uses a subquery, but not in the from clause. Not likely the most efficient way though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 11:34 am
Honestly, I'd use Top 1 With Ties, use an appropriate Order By on the Sum() function, and let the interviewer know that you got some help online to make sure you were doing it correctly. Knowing where/how to get help is probably the second most important skill a dev/DBA can have, right after the ability to learn from the help. (Being able to put up with devs asking why their "nolock" hints aren't as clever as they think they are is off the top of the list.)
If you present a well-written query that you couldn't have written yourself, and get caught out on that by not understaning how/why it works, that'll be much, much worse than presenting the query and saying right up-front that you got some help on it.
So:
SELECT TOP 1 WITH TIES Name, SUM(Consumption) AS Total_Consumption
FROM tablename
GROUP BY Name
ORDER BY SUM(Consumption) DESC ;
There are other ways to do this, but this is the most straightforward, and will also probably get the best performance, even on a large table.
Further optimization would come under the heading of things like using snapshot isolation on the query, or pre-aggregating data into a warehouse or indexed view, and other advanced subjects.
- 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:41 am
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
)
November 21, 2011 at 11:44 am
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...
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply