September 27, 2012 at 3:17 pm
Hello All,
Yes this is SQL server 2000/SP4.
One of my legacy system generates data where there is four records per group. The group is determined by the combination of fields ID1 and ID2.
The four records per group have what I call a ranking column that contains consecutive integers (1-4,12-15). They are always contiguous.
The records I want to return are the MAX record and the record that equals the MAX record -2
So for my below data I would want to return these records:
1217
1219
32913
32915
45527
45525
I have a solution using UNION but I suspect my goal can be more elegantly accomplished with one select.
Any suggestions would be greatly appreciated.
CREATE TABLE #Test
(
ID1 int,
ID2 int,
Ranking int
)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (1,21,6)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (1,21,7)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (1,21,8)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (1,21,9)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (3,29,12)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (3,29,13)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (3,29,14)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (3,29,15)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (4,55,27)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (4,55,26)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (4,55,25)
INSERT INTO #Test (ID1,ID2,Ranking) VALUES (4,55,24)
SELECT
T1.ID1,
T1.ID2,
MAX(T1.Ranking)
FROM #Test T1
INNER JOIN #Test T2
ON T1.Ranking -2 = T2.ranking
GROUP BY
T1.ID1,
T1.ID2
UNION ALL
SELECT
T2.ID1,
T2.ID2,
MAX(T2.Ranking)
FROM #Test T1
INNER JOIN #Test T2
ON T1.Ranking -2 = T2.ranking
GROUP BY
T2.ID1,
T2.ID2
DROP TABLE #Test
September 27, 2012 at 3:30 pm
I would also like to identify which record is which, the top or the bottom.
'bottom' 1 21 7
'top' 1,21, 9
'bottom' 3 29 13
'top' 3 29 15
'top' 4 55 27
'bottom' 4 55 25
Thanks
September 27, 2012 at 3:44 pm
Not sure if it's more elegant or not, and it does tecnically use 2 selects, but you could use a subquery.
select case when result.max_rank = t.ranking then 'top' else 'bottom' end hilo, t.*
from
(
select t1.id1, t1.id2, max(t1.ranking) max_rank
from #Test t1
group by t1.id1, t1.id2
) result
join #Test t on t.id1 = result.id1 and t.id2 = result.id2
where result.max_rank in (t.ranking,t.ranking+2)
October 1, 2012 at 1:29 am
Here's another way that might work:
SELECT ID1, ID2, Ranking
FROM #Test a
GROUP BY ID1, ID2, Ranking
HAVING MAX(Ranking % 2) = (
SELECT MAX(Ranking)%2
FROM #Test b
WHERE a.ID1 = b.ID1 AND a.ID2 = b.ID2
GROUP BY ID1, ID2)
Elegance is, I suppose, in the eye of the beholder.
Edit: You don't even need the subquery in the HAVING clause if you're "contiguous" ranking always starts with an even and ends with an odd. In that case, replace the subquery with 1. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply