Problem : You have a database table like following and want to return only one DeviceIMEINo for
one type of UnitModel.
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 :
one type of UnitModel.
| UnitModel | DeviceIMEINo | |
| GPRMC | 357224020036161 | |
| GPRMC | 357224020065137 | |
| KS-168 | 9650492806 | |
| KS-168 | 13632782450 | |
| M1AVL | 355689017535734 | |
| M1AVL | 359587013296408 | |
| M1AVL | 359587013296655 | |
| M1AVL | 359587013296846 | |
| M1AVL | 359587013296879 | |
| M1AVL | 359587013297331 | |
| M1AVL | 359587013297430 | |
| M1AVL | 359587013297992 | |
| M1AVL | 359587013299576 | |
| M1AVL | 359587013302065 | |
| M1AVL | 359587013307155 |
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 :
| unitmodel | deviceimeino | ID |
| GPRMC | 357224020036161 | 1 |
| KS-168 | 9650492806 | 1 |
| M1AVL | 359587014947462 | 1 |
No comments:
Post a Comment