November 21, 2011 at 9:37 am
Hiyall I am doing some query but someone told me that I must do this without a select inside a from.
The query I need is: Who consumed more? (this will need a count of course)
Name | Consumption |
John | 7 |
Amy | 8 |
Josh | 1 |
Tom | 1 |
John | 2 |
Amy | 3 |
Josh | 1 |
Amy | 3 |
(the answer is amy:14)
Can anyone do this without a 'view' or 'join' or 'order by'? (probably with some group by having statement)
Thanks in advance (and btw great forum!)
November 21, 2011 at 9:39 am
Not without an Order By, but a simple Sum() and Order By would do it. Do that all the time.
Is it a homework/test/interview type question, where artificial constraints like "no Order By clause" are necessary to get the points/whatever? Or can you use one and get away with it (like an actual business issue)?
- 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 9:40 am
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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2011 at 9:48 am
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.
November 21, 2011 at 9:50 am
GSquared (11/21/2011)
Not without an Order By, but a simple Sum() and Order By would do it. Do that all the time.Is it a homework/test/interview type question, where artificial constraints like "no Order By clause" are necessary to get the points/whatever? Or can you use one and get away with it (like an actual business issue)?
Its a interview question. Should I create a function to calculate the maximum?
November 21, 2011 at 9:51 am
bomborde (11/21/2011)
An order will be inefficient because it will order the whole table.
Do you know that or are you guessing? The entire table has to be retrieved and hashed anyway.
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 9:55 am
bomborde (11/21/2011)
GSquared (11/21/2011)
Not without an Order By, but a simple Sum() and Order By would do it. Do that all the time.Is it a homework/test/interview type question, where artificial constraints like "no Order By clause" are necessary to get the points/whatever? Or can you use one and get away with it (like an actual business issue)?
Its a interview question. Should I create a function to calculate the maximum?
No offense but if you can't do that on your own you don't deserve a job as a DBA.
Maybe as 0 experience junior with supervision at best.
November 21, 2011 at 9:56 am
GilaMonster (11/21/2011)
bomborde (11/21/2011)
An order will be inefficient because it will order the whole table.Do you know that or are you guessing? The entire table has to be retrieved and hashed anyway.
Yeh I am guessing [insert irony here]...
So if I tell you that finding a maximum via a Bubble Sort(selecting the top one) and finding a maximum through a custom function like
for (i=0, i<n_tuples, i++)
if ( x > x)
top = x
is the same?!?
November 21, 2011 at 9:58 am
Lookup cross & outer apply.
November 21, 2011 at 10:02 am
Ninja's_RGR'us (11/21/2011)
Lookup cross & outer apply.
Thanks!
November 21, 2011 at 10:06 am
bomborde (11/21/2011)
So if I tell you that finding a maximum via a Bubble Sort(selecting the top one) and finding a maximum through a custom function likefor (i=0, i<n_tuples, i++)
if ( x > x)
top = x
is the same?!?
No, they're not the same, but SQL doesn't use a bubble sort and all the tuples either have to be sorted or hashed to do the aggregation anyway, so the impact of a sort for this is not the difference between a linear search and a bubble sort on the base set of tuples.
Let me guess. Comp Sci degree, little to no work experience?
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:25 am
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.
November 21, 2011 at 10:27 am
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...
November 21, 2011 at 10:29 am
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.
November 21, 2011 at 10:33 am
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?
You could use RANK to get all the ties.
W/o a subquery or ROW_NUMBER(), don't see how you can handle ties reasonably anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply