Tuesday, February 22, 2011

Taking one sample from each group

Problem : You have a database table like following and want to return only one DeviceIMEINo for
one type of UnitModel.



UnitModelDeviceIMEINo 
GPRMC357224020036161 
GPRMC357224020065137 
KS-1689650492806 
KS-16813632782450 
M1AVL355689017535734 
M1AVL359587013296408 
M1AVL359587013296655 
M1AVL359587013296846 
M1AVL359587013296879 
M1AVL359587013297331 
M1AVL359587013297430 
M1AVL359587013297992 
M1AVL359587013299576 
M1AVL359587013302065 
M1AVL359587013307155 

This table is actually result of group by query:
select UnitModel , DeviceIMEINo from livedatagroup by UnitModel , DeviceIMEINo order by UnitModel , DeviceIMEINo

You need to write a subquery for this :
select unitmodel , deviceimeino , ID from (select UnitModel , DeviceIMEINo ,row_number() OVER ( partition by unitmodel order by unitmodel) as id from livedatagroup by UnitModel , DeviceIMEINo ) Awhere id = 1

The inner table creates a row_number column  . The outer query simply takes one rec from each group
with id = 1 (i.e. min id ) You can take min  or max as well if required.

The result is as follows :

unitmodeldeviceimeinoID
GPRMC3572240200361611
KS-16896504928061
M1AVL3595870149474621

No comments:

Post a Comment