CODEWITHSUNDEEP
sqlsql-servert-sql
soniality
soniality

Reputation: 109

SQL. How to select multiple rows by using the MIN and GROUP BY

ID UserId Name   Amount  RewardId
----------------------------
1  1      James  10.00   1
2  1      James  10.00   2
3  1      James  10.00   3
4  2      Dave   20.00   1
5  2      Dave   20.00   3
6  3      Lim    15.00   2

I'm trying to insert to another table, and this is the result that i'm struggling with:

Tbl1ID  RewardId
------------------
1       1
1       2
1       3
4       1
4       3
6       2

I'm trying to get the MIN(ID) of each person and select all the RewardId that belong to that person.

Upvotes: 1

Views: 60

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try this

SELECT tbl1id,RewardID From
table1 S JOIN
(
SELECT MIN(ID) as tbl1id,Name FROM table1 GROUP BY Name
) T ON T.Name = S.Name
ORDER BY tbl1id

FIDDLE DEMO

Output:

    Tbl1ID  RewardId
    ----------------
    1       1
    1       2
    1       3
    4       1
    4       3
    6       2

If you want insert into new table then try this out

Insert into Newtable (tbl1id,RewardID)
SELECT tbl1id,RewardID from
table1 S JOIN
(
SELECT MIN(ID) as tbl1id,Name
FROM table1
GROUP BY Name
) T ON T.Name = S.Name

ORDER BY tbl1id;

FIDDLE DEMO

Upvotes: 0

John Woo
John Woo

Reputation: 263933

If you are running SQL Server 2008+, you can simplify it by using Window Function.

INSERT INTO AnotherTable (Tbl1ID, RewardID)
SELECT MIN(ID) OVER (PARTITION BY Name),
       RewardID
FROM   SourceTable

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

You could do a simple self join to get the minimum id value per userid/rewardid combination;

SELECT MIN(a.id) Tbl1ID, b.RewardId
FROM mytable a
JOIN mytable b
  ON a.name = b.name
GROUP BY b.userid, b.rewardid
ORDER BY tbl1id, rewardid;

An SQLfiddle to test with.

Upvotes: 3

Related Questions