Loading

How can i get the minimum id value under only same combo

How can i get the minimum id value under only same combo

siva nathan

siva nathan

10y870Visual Basic .NET

id combo

1 1

2 2

3 2

4 1

in this table i want only min id value for same cobmo,for example from the above table combo's are 1 and 2 i want only 2nd combo within 2nd combo i want only min id

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Accepted answer
1. SELECT a.id,a.combo
2. FROM(
3. SELECT
4. ROW_NUMBER()OVER(PARTITION BY SET1.combo ORDER BY SET1.id ASC)AS ROWNUMBERS,
5. SET1.id,
6. SET1.combo
7. FROM (
8. SELECT id ,combo FROM
9. tablecombo
10. GROUP BY
11. combo
12. HAVING
13. COUNT(*) > 1
14. ) SET1
15. )a
16. WHERE a.ROWNUMBERS=1

Esakki Muthu

Esakki Muthu10y ago

Hi Siva,

Try this,

Create table Combo(ID Int,combo int)

insert into Combo values(1,1)

insert into Combo values(2,2)

insert into Combo values(3,1)

insert into Combo values(4,2)

insert into Combo values(5,3)

insert into Combo values(6,4)

insert into Combo values(8,1)

insert into Combo values(9,6)

insert into Combo values(10,5)

insert into Combo values(11,2)

with com as (Select combo from Combo group by combo )

Select min(c.ID),co.combo from Combo c

join com co on c.combo = co.combo

group by co.combo

Manas Mohapatra

Manas Mohapatra10y ago

In SQL:

SELECT MIN(id) As MinId
FROM tblTemp
WHERE combo = 2
GROUP BY combo;

In LINQ:

int minId = (from p in records
group p by p.combo into g
where p.combo = 2
select new { combo = g.key, minid = g.id.Min() }).SingleOrDefault().minid;

Prakash Kumar

Prakash Kumar10y ago

Use the following query, while using group by we will get the min id for same values.

select min(id)id,combo from tablecombo where combo =2 group by combo