February 11, 2005 at 5:23 am
how do i select 2nd max sum , 3rd max sum ,4th max sum
using a sql query.
any idea
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 11, 2005 at 5:39 am
Why not DECLARE 4 different variables and then define as follows:
SET @max1 = (SELECT MAX(SUM(field1)) FROM dbo.tableA)
SET @max2 = (SELECT MAX(SUM(field1)) FROM dbo.tableA WHERE MAX(SUM(field1)) < @max1)
SET @max3 = (SELECT MAX(SUM(field1)) FROM dbo.tableA WHERE MAX(SUM(field1)) < @max2)
SET @max4 = (SELECT MAX(SUM(field1)) FROM dbo.tableA WHERE MAX(SUM(field1)) < @max3)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
February 11, 2005 at 6:06 am
AJ,
When I run
USE NORTHWIND
GO
DECLARE @MAX1 FLOAT, @MAX2 FLOAT, @MAX3 FLOAT, @MAX4 FLOAT
SET @MAX1 = (SELECT MAX(UnitPrice) FROM [Order Details])
SET @MAX2 = (SELECT MAX(UnitPrice) FROM [Order Details] WHERE MAX(UnitPrice) < @max1)
I get:
Server: Msg 147, Level 16, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
So, I don't think, this will work this way.
Off my head, I think you need something like this:
USE NORTHWIND
GO
DECLARE @MAX1 FLOAT, @MAX2 FLOAT, @MAX3 FLOAT, @MAX4 FLOAT
SET @MAX1 = (SELECT MAX(UnitPrice) FROM [Order Details])
SET @MAX2 =
(SELECT TOP 1(UnitPrice)
FROM [Order Details]
GROUP BY UnitPrice
HAVING MAX(UnitPrice) < @MAX1 ORDER BY UnitPrice DESC)
SELECT @MAX1, @MAX2
Well, I guess the commonly mentioned way would rather be
SELECT TOP 1 UnitPrice FROM [Order Details] ORDER BY UnitPrice DESC
SELECT TOP 1 UnitPrice FROM [Order Details] WHERE UnitPrice NOT IN
(
SELECT TOP 1 UnitPrice FROM [Order Details] ORDER BY UnitPrice DESC)
ORDER BY UnitPrice DESC
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 11, 2005 at 4:08 pm
You don't need to use the Max() function in the where clause of the second SET statement. "WHERE UnitPrice < @max1" will limit your search to those UnitPrice's which are less than the value of @max1, and the MAX(UnitPrice) in the SELECT clause will select the largest of those values.
USE NORTHWIND
GO
DECLARE @MAX1 FLOAT, @MAX2 FLOAT, @MAX3 FLOAT, @MAX4 FLOAT
SET @MAX1 = (SELECT MAX(UnitPrice) FROM [Order Details])
SET @MAX2 = (SELECT MAX(UnitPrice) FROM [Order Details] WHERE UnitPrice < @max1)
February 13, 2005 at 4:16 am
i have heard there are direct functions in oracle for this,
is there no such facility in sql2000?
do i need to the things only that r sugested above suppose iam 6th max, 9th max or some 50th max then ?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 13, 2005 at 8:46 am
What is a "max sum"?
I think the OP wants the 2/3/4 highest sums based on some grouping, in which case I assume that the grouped column(s) would also be necessary information. So something like this might be more appropriate:
SELECT TOP 3 x.Col, SUM(x.ColOther) AS SumOfOther
FROM Tbl x
GROUP BY x.Col
HAVING SUM(x.ColOther) <
(SELECT TOP 1 SUM(y.ColOther)
FROM Tbl y
GROUP BY y.Col
ORDER BY SUM(y.ColOther) DESC)
ORDER BY SUM(x.ColOther) DESC
--
Adam Machanic
whoisactive
February 26, 2005 at 3:06 am
this thing is not very proper, if he tells me to find max 15th record, then how u gona do it,
suppose max 240th record, then ?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 26, 2005 at 3:04 pm
Did you look at the example I posted?
Replace the 1 in the 'TOP 1' in the derived table with the (n) - 1 row you want and the 3 in the 'TOP 3' in the outer query with the (m) row you want, assuming you want rows (n) - (m).
--
Adam Machanic
whoisactive
February 28, 2005 at 2:00 am
I think Adam's solution is what you are looking for, but you didn't give too many clues to what you'll be doing with the result, what's the data structure and amount of data etc. If it doesn't help, try to specify the problem in more detail...
Of course, you can always use a temp table with identity column, insert the aggregated data into it ordered by sum DESC, and select the 124th biggest sum with WHERE ID_column=124 - but IMHO that would be helpful only if you can reuse the temp table for several operations. Otherwise I'd prefer Adam's solution with derived table and "double TOP".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply