Reputation: 165
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
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
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