Arnab Arnab - 1 year ago 57
SQL Question

Grouping values by a column and getting min value of another with another column

My table structure is as follows

userId period pcount
a 01/03 100
a 02/03 150
a 03/03 200
b 02/03 250
...and so on..

I wish to get the row which has the minimum pcount for each userid, so the result for userid a ought to be as below..

a 01/03 100

If I did not want the period value, I could have grouped the table by userId and selected userId and MIN of pcount.

But, since I need the period as well, what should I do..

Though I'm using SQL Server on azure,it might change and so would appreciate standard ansi sql query.


Answer Source

In general these kind of requests are handled through ROW_NUMBER

Select * From 
select row_number() Over(Partition by userId Order by pcount asc) as rn,*
From yourtable
) A
Where Rn = 1

Another approach using INNER JOIN works with most of the DBMS

FROM   yourtable A
       INNER JOIN (SELECT userId,
                          Min(pcount) AS M_pcount
                   FROM   yourtable
                   GROUP  BY userId) B
               ON A.userId = B.userId
                  AND A.pcount = B.M_pcount 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download