Minimum of top two values
-
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
ItemName VARCHAR(6),
ItemNo INT,
ItemValue NUMERIC(20, 2)
)
INSERT @MyTable
SELECT 'Item1', 1, 543.34 UNION ALL
SELECT 'Item1', 3, 243.34 UNION ALL
SELECT 'Item1', 2, 12 UNION ALL
SELECT 'Item1', 4, 0 UNION ALL
SELECT 'Item2', 1, 25.34 UNION ALL
SELECT 'Item2', 4, 25.34 UNION ALL
SELECT 'Item2', 3, 12 UNION ALL
SELECT 'Item2', 2, 11 UNION ALL
SELECT 'Item3', 4, 84443.34 UNION ALL
SELECT 'Item3', 3, 3443.34 UNION ALL
SELECT 'Item3', 2, 1322 UNION ALL
SELECT 'Item3', 1, 22 UNION ALL
SELECT 'Item4', 1, 12.34 UNION ALL
SELECT 'Item4', 2, 12.34 UNION ALL
SELECT 'Item4', 3, 12.34 UNION ALL
SELECT 'Item4', 4, 10 UNION ALL
SELECT 'Item5', 1, 543.34 UNION ALL
SELECT 'Item5', 4, 12.34 UNION ALL
SELECT 'Item5', 2, 12 UNION ALL
SELECT 'Item5', 3, 0 UNION ALL
SELECT 'Item6', 1, 25.25 UNION ALL
SELECT 'Item6', 2, 25.25 UNION ALL
SELECT 'Item6', 3, 25.25 UNION ALL
SELECT 'Item6', 4, 25.25 UNION ALL
SELECT 'Item7', 1, 543.34 UNION ALL
SELECT 'Item7', 4, 243.34 UNION ALL
SELECT 'Item7', 3, 0 UNION ALL
SELECT 'Item7', 2, 0
/*
Each Item has 4 numbers. We have to take the largest two items and get minimum of these two and entire row.
Even when the top values are shared only top two items are considered. Ex. Item2
Can any one please help with a query.
Expected result.
ItemName ItemNo ItemValue
-------- ----------- ----------------------
Item1 3 243.34
Item2 4 25.34
Item3 3 3443.34
Item4 2 12.34
Item5 4 12.34
Item6 2 25.25
Item7 4 243.34
*/
Regards,
gova -
Why for Item4 and Item6 ItemNo = 2 is reported? Why not 1, not 3?
_____________
Code for TallyGenerator -
Item Number does not matter.
It is like recruitng top student from each class(Item). (First one is already gone so take second). If all the students scored same any one.
Regards,
gova -
Where have you stored list of "first" Items (students), which are gone and not to be returned by your query?
If you have "any one" logic in place then same student may appear as "first" and "second" at the same time.
You may choose any one from the rest, but you must know exactly who is already chosen.
_____________
Code for TallyGenerator -
Sergiy
ItemValue is the deciding factor here. I have to select the second highest value for each item. I can do that.
Where I am getting stuck is if highest value is shared by tow or more items I have to select highest value.
if 4, 3, 2, 1 are there I have to select 3. If 4, 4, 3, 2 are there I have to select any 4. If 4, 3, 3, 3 are there I can select any 3.
For each Item SELECT the row with Minimum Item Value from (SELECT TOP 2 * FROM @MyTable ORDER BY ItemValue DESC) T
Hope I explained right this time.
Regards,
gova -
I gave you a hint:
"Where have you stored list of "first" Items?"
You need to have "TOP 1" first, then you select "TOP 2" and return those from "TOP 2" which are not listed in "TOP 1".
And
ORDER BY ItemValue DESC
is not enough. As you just said "ItemValue is the deciding factor here", so this statement must be reflected in the query.
_____________
Code for TallyGenerator -
First items are not stored any where.
I have data like gave in the sample. And I have to get a result set as I mentioned in the original post.
To be more precise I can write a query for a single item as below. But I need help in writing a query for all items.
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item1' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item2' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item3' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item4' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item5' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item6' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
UNION
SELECT * FROM (
SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM @MyTable WHERE ItemName = 'Item7' ORDER BY ItemValue DESC, ItemNo DESC) A
ORDER BY ItemValue, ItemNo) B
ORDER BY 1
/*
Results from this query
ItemName ItemNo ItemValue
-------- ----------- ----------------------
Item1 3 243.34
Item2 1 25.34
Item3 3 3443.34
Item4 2 12.34
Item5 4 12.34
Item6 3 25.25
Item7 4 243.34
*/
Regards,
gova -
DECLARE @temp TABLE ([ID] int IDENTITY(1,1),ItemName VARCHAR(6),ItemNo INT,ItemValue NUMERIC(20, 2))
INSERT INTO @temp (ItemName,ItemNo,ItemValue)
SELECT a.ItemName,a.ItemNo,a.ItemValue
FROM @MyTable a
ORDER BY a.ItemName ASC,a.ItemValue DESC,a.ItemNo ASC
SELECT a.ItemName,a.ItemNo,a.ItemValue
FROM @temp a
INNER JOIN (SELECT MIN([ID]) AS [ID] FROM @temp b GROUP BY b.ItemName) c
ON a.[ID] = (c.[ID] + 1)
Probably best to use temp tables and add indexes
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David. Good idea. I will use your idea.
I did like this which almost solved my work.
SELECT A.ItemName, A.ItemValue
FROM
@MyTable A
JOIN
(
SELECT ItemName, MAX(ItemValue) ItemValue
FROM
@MyTable
GROUP BY ItemName) B
ON
A.ItemName = B.ItemName
AND A.ItemValue = B.ItemValue
GROUP BY A.ItemName, A.ItemValue
HAVING COUNT(ItemNo) > 1
UNION
SELECT T1.ItemName, MAX(T1.ItemValue) ItemValue
FROM
@MyTable T1
JOIN
(
SELECT A.ItemName, A.ItemValue
FROM
@MyTable A
JOIN
(
SELECT ItemName, MAX(ItemValue) ItemValue
FROM
@MyTable
GROUP BY ItemName) B
ON
A.ItemName = B.ItemName
AND A.ItemValue = B.ItemValue
GROUP BY A.ItemName, A.ItemValue
HAVING COUNT(ItemNo) = 1) T2
ON
T1.ItemName = T2.ItemName
AND T1.ItemValue < T2.ItemValue
GROUP BY
T1.ItemName
ORDER BY 1
Regards,
gova -
This is SO much easier if you have a RowNum column....
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
RowNum INT IDENTITY(1,1) PRIMARY KEY,
ItemName VARCHAR(6),
ItemNo INT,
ItemValue NUMERIC(20, 2)
)
INSERT @MyTable
SELECT 'Item1', 1, 543.34 UNION ALL
SELECT 'Item1', 3, 243.34 UNION ALL
SELECT 'Item1', 2, 12 UNION ALL
SELECT 'Item1', 4, 0 UNION ALL
SELECT 'Item2', 1, 25.34 UNION ALL
SELECT 'Item2', 4, 25.34 UNION ALL
SELECT 'Item2', 3, 12 UNION ALL
SELECT 'Item2', 2, 11 UNION ALL
SELECT 'Item3', 4, 84443.34 UNION ALL
SELECT 'Item3', 3, 3443.34 UNION ALL
SELECT 'Item3', 2, 1322 UNION ALL
SELECT 'Item3', 1, 22 UNION ALL
SELECT 'Item4', 1, 12.34 UNION ALL
SELECT 'Item4', 2, 12.34 UNION ALL
SELECT 'Item4', 3, 12.34 UNION ALL
SELECT 'Item4', 4, 10 UNION ALL
SELECT 'Item5', 1, 543.34 UNION ALL
SELECT 'Item5', 4, 12.34 UNION ALL
SELECT 'Item5', 2, 12 UNION ALL
SELECT 'Item5', 3, 0 UNION ALL
SELECT 'Item6', 1, 25.25 UNION ALL
SELECT 'Item6', 2, 25.25 UNION ALL
SELECT 'Item6', 3, 25.25 UNION ALL
SELECT 'Item6', 4, 25.25 UNION ALL
SELECT 'Item7', 1, 543.34 UNION ALL
SELECT 'Item7', 4, 243.34 UNION ALL
SELECT 'Item7', 3, 0 UNION ALL
SELECT 'Item7', 2, 0
SELECT t1.ItemName,t1.ItemNo,t1.ItemValue
FROM @MyTable t1
WHERE t1.RowNum IN (SELECT TOP 1 d1.RowNum
FROM (SELECT TOP 2 RowNum,ItemValue
FROM @MyTable t2
WHERE t1.ItemName = t2.ItemName
ORDER BY ItemValue DESC) d1
ORDER BY d1.ItemValue)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Run this
select d.itemname,
max(x.itemno) as itemno,
d.qwerty
from (
select t1.itemname,
(select min(itemvalue) from (select top 2 t2.itemvalue from @mytable as t2 where t2.itemname = t1.itemname order by t2.itemvalue desc) as d) as qwerty
from @mytable as t1
 
as dinner join @mytable as x on x.itemname = d.itemname and x.itemvalue = d.qwerty
group by d.itemname,
d.qwerty
order by d.itemname
N 56°04'39.16"
E 12°55'05.25" -
select *
from @mytable
where row_number() over (partition by itemname order by itemvalue desc) = 2
order by itemname
N 56°04'39.16"
E 12°55'05.25"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply