ncoder
ncoder

Reputation: 165

Return rows for maximum column value having same value from another column

I have a database table like follows:

Create table #temp(ID INT, Number1 INT, Number2 INT)

INSERT INTO #temp(111, 123, 10)

INSERT INTO #temp(111, 223, 10)

INSERT INTO #temp(111, 323, 10)

INSERT INTO #temp(112, 123, 11)

INSERT INTO #temp(112, 223, 11)

INSERT INTO #temp(112, 333, 11)

INSERT INTO #temp(113, 124, 12)

INSERT INTO #temp(113, 126, 12)

INSERT INTO #temp(114, 128, 121)

I have been working on a query but still no luck. I am looking for to return the following result:

ID Number1 Number2

111 323 10

112 333 11

113 126 12

114 128 121

In the result set need to return the row with maximum value of column Number1 for the same ID value.

Upvotes: 0

Views: 129

Answers (2)

logixologist
logixologist

Reputation: 3834

SELECT ID, Max(Number1) as Number1, Number2 FROM #temp group by ID, Number2

I believe you are looking for the GROUP clause. It returns this:

111 323 10
112 333 11
113 126 12
114 128 121

Thank you @MichaelMoreno for pointing it out. I assumed that was a typo on the OP's part and I didnt post that as the solution. He was really asking for the way to SELECT. Here is the corrected code:

Create table #temp(ID INT, Number1 INT, Number2 INT)

INSERT INTO #temp values(111, 123, 10)

INSERT INTO #temp values(111, 223, 10)

INSERT INTO #temp values(111, 323, 10)

INSERT INTO #temp values(112, 123, 11)

INSERT INTO #temp values(112, 223, 11)

INSERT INTO #temp values(112, 333, 11)

INSERT INTO #temp values(113, 124, 12)

INSERT INTO #temp values(113, 126, 12)

INSERT INTO #temp values(114, 128, 121)

Upvotes: 0

Michael Moreno
Michael Moreno

Reputation: 181

This SQL fixes your SQL :-) and I think returns the answer you are after:

DECLARE @temp table (ID INT, Number1 INT, Number2 INT);
INSERT INTO @temp (ID,Number1, Number2) VALUES (111, 123, 10)
INSERT INTO @temp (ID,Number1, Number2) VALUES (111, 223, 10)
INSERT INTO @temp (ID,Number1, Number2) VALUES (111, 323, 10)
INSERT INTO @temp (ID,Number1, Number2) VALUES (112, 123, 11)
INSERT INTO @temp (ID,Number1, Number2) VALUES (112, 223, 11)
INSERT INTO @temp (ID,Number1, Number2) VALUES (112, 333, 11)
INSERT INTO @temp (ID,Number1, Number2) VALUES (113, 124, 12)
INSERT INTO @temp (ID,Number1, Number2) VALUES (113, 126, 12)
INSERT INTO @temp (ID,Number1, Number2) VALUES (114, 128, 121)


SELECT DISTINCT T1.ID, T1.Number1, T1.Number2
FROM @temp T1
JOIN @temp T2 ON T1.ID = T2.ID AND T1.Number1=(SELECT MAX(Number1) FROM @temp T3 WHERE T3.ID=T1.ID)

All the best, Michael

Upvotes: 1

Related Questions