Need to select highest value in column for row for dup elimination
-
I'm not even sure I can write this down. Here is my data:
A B C
4955 123 100
4955 123 200
4955 124 100
4955 124 200
4955 125 100
4955 125 200
My desired end result would be:
A B C
4955 123 200
4955 124 200
4955 125 200
Basically I need the highest number value in column C, along with
the attached columns A and B. Unfortunately they aren't "true"
dups, and of course I'm not interested in the MAX value of the table, but the MAX value of column C as it relates to column A and B. Thanks, hope it makes sense.
-
DECLARE @T1 TABLE (A INT, B INT, C INT)
INSERT INTO @T1 VALUES (4955, 123, 100)
INSERT INTO @T1 VALUES (4955, 123, 200)
INSERT INTO @T1 VALUES (4955, 124, 100)
INSERT INTO @T1 VALUES (4955, 124, 200)
INSERT INTO @T1 VALUES (4955, 125, 100)
INSERT INTO @T1 VALUES (4955, 125, 200)
PRINT 'BEFORE DELETE'
SELECT * FROM @T1
DELETE @T1
FROM @T1 OUTER_T
WHERE NOT EXISTS (SELECT 1 FROM @T1 INNER_T
WHERE INNER_T.A = OUTER_T.A
AND INNER_T.B = OUTER_T.B
AND INNER_T.C < OUTER_T.C)
PRINT 'AFTER DELETE'
SELECT * FROM @T1
--RESULTS
BEFORE DELETE
A B C
----------- ----------- -----------
4955 123 100
4955 123 200
4955 124 100
4955 124 200
4955 125 100
4955 125 200
AFTER DELETE
A B C
----------- ----------- -----------
4955 123 200
4955 124 200
4955 125 200
-
Hi,
Wouldn't the following select work?
Select
A, B, Max(C)
From
YourTable
Group By
A, B
Order By
A, B
Richard
-
No, the MAX condition always seems to get satisfied. (also their are many other columns in my project). I tried this first of course, but it never seems to work.
-
Interesting. Can this work for actual data removal at the table level (instead of temp table)?
-
Hi guys,
I might be missing something here but shouldn't it be as easy as:
SELECT
[FIRST].A,
[FIRST].B,
[FIRST].C
FROM
YOUR_TABLE AS [FIRST]
WHERE
EXISTS (
SELECT
MAX([SECOND].C)
FROM
YOUR_TABLE AS [SECOND]
WHERE
[SECOND].C = [FIRST].C)
This will return all data from the table where column "C" is equal to the maximum value.
-
Sorry about the above sql formatting. But the site doesn't seem to allow indenting.
-
What exactly does not work?
_____________
Code for TallyGenerator -
In all the work I've done attempting to "rank" data, I've found MAX not to be useful. I think its really only good for math, or single value search's. The previous post works quite well, in tricking it into finding the higher value.
-
Hi all,
What a confusing question!
If I've managed to understand it correctly, here's my suggestion. I've changed the data to (hopefully) make it a bit less confusing for those suggesting max (which was my first instinct based on a mis-understanding of the question).
Hope it helps...
--data
DECLARE @T1 TABLE (A INT, B INT, C INT, x int)
INSERT INTO @T1 VALUES (4955, 123, 100, 1)
INSERT INTO @T1 VALUES (4955, 123, 200, 2)
INSERT INTO @T1 VALUES (4955, 124, 100, 3)
INSERT INTO @T1 VALUES (4955, 124, 300, 4)
INSERT INTO @T1 VALUES (4955, 125, 100, 5)
INSERT INTO @T1 VALUES (4955, 125, 300, 6)
--calculation
select * from @T1 t
where c = (select max(c) from @t1 where a = t.a and b = t.b)
order by a, b
--results
A B C x
----------- ----------- ----------- -----------
4955 123 200 2
4955 124 300 4
4955 125 300 6
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
So, Ryan, you have used MAX() as well.
What's a difference?
You solution is just more complicated and more slow, but returns exactly the same result.
_____________
Code for TallyGenerator -
You're too kind, Sergiy.
The difference is just that column x is included. I know it's not difficult - I was just trying to explain (to myself and others) why A had rejected the 'obvious' group by method.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply