Select a distinct query for two columns where one of the distinct columns is a minimum value

Question

Using the following table data

id	group	recno
100	1	1
100	1	2
100	3	3
100	3	4
101	2	5
101	3	6
102	1	7

I need to Select a distinct id with a distinct minimum group number and the rest of the columns matching minimum group number returned

My ideal data return would be

id	group	recno
100	1	1
101	2	5
102	1	7

if i was not interested in returning the recno and other columns (not added here) I could do the following

SELECT id, MIN(group)
FROM table
where group > 0
GROUP BY PNCID;

but as soon as i add the recno column I get aggregate function issues

I have been melting my mind on this all afternoon and as yet have not come up with an answer so I am reaching out to the community in the hope that any help or suggestions could be given to my quandary.

On top of the above, the table I will eventually be running this on will have upwards of 500,000 records so not only do I need to build a query that can accommodate the above but do it in the fastest way possible and then further complicated by the fact that the query needs to run on both MSSQL and Oracle (I generally stick to the ANSI 92 query standards with cross platform queries) but if I need two different queries then so be it.

Stored procedure's are sadly not an option for me so I must rely on an sql query solution

Answer 1

Partition by id, do order by group, recno to get the lowest group for each id.

SELECT id, group, recno
FROM (select t.*, row_number() over (partition by id order by group, recno) rn
      from table t
      where group > 0
     ) dt
where rn = 1

BTW, group is a reserved word, so it needs to be delimited as "group". (Or [group], MS SQL Server special.) https://en.wikipedia.org/wiki/List_of_SQL_reserved_words