Autor |
Tema Buscar Opciones del Tema
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 03/Septiembre/2020 a las 10:05 |
Hola,
Os cuento lo que veo, la salida es la siguiente:
- Aparece siempre el mismo DeviceStatus.Id agrupado por dispositivos - Aparece el maximo ID correctamente por dispositivo. - Se listan los dispositivo por bloques, dependiendo del DeviceStatus.Id.
Os pongo un ejemplo abajo: Devices.Id Manufacturer Model DeviceStatus.Id MaxId Criticity PIlot 1 NWM NINGBO 309 1253 High No 2 Suntech ST4340 309 1257 Medium No 90 Humanox Humanox Soccer 309 1259 Very High Sí 91 WNC SKM-5xE50 309 405 Low No 94 Amper ILK3000 309 344 Low No 95 Quectel BG95-M3 309 1247 Medium No 96 Quectel BC92 309 420 Medium No 97 Quectel BG77 309 1245 Medium No 99 Oliver Oli 309 1249 High Sí 100 Teltonika RUT955 309 1243 High No 101 Teltonika RUT240 309 357 Low No 103 Teldat Teldat H2 Automotive+ 309 1285 Very Low No 104 Queclinck GV600ME 309 321 Medium No 105 Queclicnk GV50ME 309 322 Medium No 106 WeWins WeWins OBD 309 392 Medium No 107 Odins IPex12 309 401 Medium No 109 SAYME SAYME GATE 309 406 Medium No 110 Teltonika TRM25000 309 422 Medium No 111 Teltonika RUTX110 309 423 Medium No 112 Neoway T101 309 427 Medium No 113 Things Matrix Things Matrix small 309 323 Low No 114 Mobiletek L620 309 378 Low No 115 Mobiletek L710 309 379 Low No 116 Mobiletek L630 309 380 Low No 117 DataKorum PIPE22 309 361 Low No 118 Wibeee Wibeee-Box 309 315 Very Low No 119 NetComm NTC-222 309 360 Very Low No 120 CINTERION EXS82-W 309 381 Very Low No 121 Amper PO-170196 309 399 Very Low No 122 Gemalto EXS82 309 402 Very Low No 123 Inspiralia Quest3 - My Sensor 309 1239 Very High Sí 124 Amper BLN-1007nbLC 309 398 Medium Sí 125 Seowon SLM-120GI 309 328 Medium Sí 126 Queclinck GL500M 309 320 Medium No 127 Multitech rCell 309 334 Low No 128 MOBA FLS2 309 350 Low No 129 Inhemeter DDZ1513 309 337 Low No 130 Nordic nRF9160 309 359 Very Low No 131 StalKit Stalkit v2.5 309 391 High Sí 133 Metersit TF19-002 309 365 Medium No 134 GoldCard JGD1.6S-R 309 367 Medium No 135 Conexiona Conexiona Wine Tracker 309 371 Low No 136 Hongdian H7921-N 309 377 Low No 137 Vertigo Solutions Radarfy 309 369 Very Low No 138 Zebra TC57 309 374 Very Low No 139 Contazara Smart NBIoT BC68 A2.D9 309 1275 Very High Sí 140 Amper WM1000S 309 1277 Very High Sí 141 Contazara Smart NBIoT BC68 B28 309 1297 Very High Sí 142 Open Innovation 2 go PB-SOUL1 309 1299 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF 309 1355 Very High Sí 144 ZTE OBD Mojio 309 1323 Very High No 145 Net4Things OBD Movistar Car 309 1325 Very High No 146 Terranova Terranova Tests 309 1335 Very High No 147 Trackimo TRKM019 309 1349 High No 148 Matrix MTX-IOT 309 1261 High Sí 149 SAYME SAYME-Dumpster 309 1269 High Sí 150 Theoremus eSLSF11 309 1281 High Sí 151 Molukas Quatro Sensor 309 1303 High Sí 152 FEN Inspiralia 309 1329 High Sí 153 Datakorum PIPE21 309 1263 High No 154 Contazara Smart NBIoT BC68 A2.DD 309 1265 High No 155 Terranova Gas Meter en Terranova 309 1289 High No 156 Contazara Smart NBIOT Battery 309 1293 High No 157 Contazara Smart NBIoT BC68 B20 309 1295 High No 158 Contazara Smart NBIoT BC68 309 1313 High No 159 SmartMeterQ UGM-G4 309 1331 High No 160 Sayme SOS Button Sayme 309 1337 High No 161 Contazara CZ4000_NB-IoT 309 1343 Medium No 162 Things Matrix Things Matrix big 309 1267 Medium No 163 Ruptela Eco4 Light+ 2G 3G 309 1271 Medium No 164 Queclinck GV350ME_1713 309 1273 Medium No 165 Queclinck GL300ME 309 1283 Medium No 166 Sagemcom EG4EVO 309 1287 Medium No 167 Hexing RPM Hexing BC95-G 309 1301 Medium No 168 SmartMeterQ UGM v2 NBIoT 309 1305 Medium No 169 Contazara Contador Octave-Terranova 309 1315 Medium No 170 Sagemcom S211 309 1319 Medium No 171 Robustel PI_XS20190426005ES 309 1321 Medium No 172 Britvic Britvic Demo 309 1327 Medium No 173 Amper AMPER 309 1339 Medium No 174 Hexing HXE110 309 1341 Medium No 175 Stalkit StalkIT 309 1345 Medium No 176 NetComm NTC100 309 1351 Medium No 177 Accent TRK-230 309 1353 Low No 178 Ublox Ublox SARA N310 309 1255 Low No 179 MuRata Type 1SS 309 1307 Low No 180 Teleoring RBMTX-Lite-NB 309 1317 Low No 181 Digital Keys LDK400 309 1333 Low No 182 Telefonica LWM2M BG96 309 1347 Very Low No 183 Ubicquia LCB200 309 1279 No 184 Britvic Tracker Barato 309 1291 No 185 Nokia GuardBand Nokia 309 1309 No 186 Mojio, N4T OBD Product 309 1311 No 233 TCL MKNO 309 431 Medium No 234 EFENTO Efento NBIoT Sensor 309 1241 High No 257 Contazara SmartNBIoT IPsec 309 1251 Medium No 259 Urbiotica SPM.1.1.1.0 309 1235 Very High Sí 260 Pietro Fiorentini RSE-N1 309 1233 Very High No 262 Teldat M8 Smart 309 1231 Very High No 264 ZIV 5CTB MCM 309 439 High No 265 Quectel BG600L-M3 309 1356 High No 288 General Mechatronics NaBi 309 309 Medium 289 Ublox Ublox R510S 309 310 Low 290 Exelonix VODEXL-01 309 311 Low 291 ETM ETM770-8 309 312 Low 292 Notioni MW29 309 313 Low 293 Efento Efento NBIoT Sensor_05.09 309 442 High 294 Inspiralia MySensor 309 445 Very High 295 Amper WM10003_V3.9 309 448 High 296 WeWins WeWins 5G 309 452 Low 297 Teltonika TRB141000 309 1237 Medium 1 NWM NINGBO 310 1253 High No 2 Suntech ST4340 310 1257 Medium No 90 Humanox Humanox Soccer 310 1259 Very High Sí 91 WNC SKM-5xE50 310 405 Low No 94 Amper ILK3000 310 344 Low No 95 Quectel BG95-M3 310 1247 Medium No 96 Quectel BC92 310 420 Medium No 97 Quectel BG77 310 1245 Medium No 99 Oliver Oli 310 1249 High Sí 100 Teltonika RUT955 310 1243 High No 101 Teltonika RUT240 310 357 Low No 103 Teldat Teldat H2 Automotive+ 310 1285 Very Low No 104 Queclinck GV600ME 310 321 Medium No 105 Queclicnk GV50ME 310 322 Medium No 106 WeWins WeWins OBD 310 392 Medium No 107 Odins IPex12 310 401 Medium No 109 SAYME SAYME GATE 310 406 Medium No 110 Teltonika TRM25000 310 422 Medium No 111 Teltonika RUTX110 310 423 Medium No 112 Neoway T101 310 427 Medium No 113 Things Matrix Things Matrix small 310 323 Low No 114 Mobiletek L620 310 378 Low No 115 Mobiletek L710 310 379 Low No 116 Mobiletek L630 310 380 Low No 117 DataKorum PIPE22 310 361 Low No 118 Wibeee Wibeee-Box 310 315 Very Low No 119 NetComm NTC-222 310 360 Very Low No 120 CINTERION EXS82-W 310 381 Very Low No 121 Amper PO-170196 310 399 Very Low No 122 Gemalto EXS82 310 402 Very Low No 123 Inspiralia Quest3 - My Sensor 310 1239 Very High Sí 124 Amper BLN-1007nbLC 310 398 Medium Sí 125 Seowon SLM-120GI 310 328 Medium Sí 126 Queclinck GL500M 310 320 Medium No 127 Multitech rCell 310 334 Low No 128 MOBA FLS2 310 350 Low No 129 Inhemeter DDZ1513 310 337 Low No 130 Nordic nRF9160 310 359 Very Low No 131 StalKit Stalkit v2.5 310 391 High Sí 133 Metersit TF19-002 310 365 Medium No 134 GoldCard JGD1.6S-R 310 367 Medium No 135 Conexiona Conexiona Wine Tracker 310 371 Low No 136 Hongdian H7921-N 310 377 Low No 137 Vertigo Solutions Radarfy 310 369 Very Low No 138 Zebra TC57 310 374 Very Low No 139 Contazara Smart NBIoT BC68 A2.D9 310 1275 Very High Sí 140 Amper WM1000S 310 1277 Very High Sí 141 Contazara Smart NBIoT BC68 B28 310 1297 Very High Sí 142 Open Innovation 2 go PB-SOUL1 310 1299 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF 310 1355 Very High Sí 144 ZTE OBD Mojio 310 1323 Very High No 145 Net4Things OBD Movistar Car 310 1325 Very High No 146 Terranova Terranova Tests 310 1335 Very High No 147 Trackimo TRKM019 310 1349 High No 148 Matrix MTX-IOT 310 1261 High Sí 149 SAYME SAYME-Dumpster 310 1269 High Sí 150 Theoremus eSLSF11 310 1281 High Sí 151 Molukas Quatro Sensor 310 1303 High Sí 152 FEN Inspiralia 310 1329 High Sí 153 Datakorum PIPE21 310 1263 High No 154 Contazara Smart NBIoT BC68 A2.DD 310 1265 High No 155 Terranova Gas Meter en Terranova 310 1289 High No 156 Contazara Smart NBIOT Battery 310 1293 High No 157 Contazara Smart NBIoT BC68 B20 310 1295 High No 158 Contazara Smart NBIoT BC68 310 1313 High No 159 SmartMeterQ UGM-G4 310 1331 High No 160 Sayme SOS Button Sayme 310 1337 High No 161 Contazara CZ4000_NB-IoT 310 1343 Medium No 162 Things Matrix Things Matrix big 310 1267 Medium No 163 Ruptela Eco4 Light+ 2G 3G 310 1271 Medium No 164 Queclinck GV350ME_1713 310 1273 Medium No 165 Queclinck GL300ME 310 1283 Medium No 166 Sagemcom EG4EVO 310 1287 Medium No 167 Hexing RPM Hexing BC95-G 310 1301 Medium No 168 SmartMeterQ UGM v2 NBIoT 310 1305 Medium No 169 Contazara Contador Octave-Terranova 310 1315 Medium No 170 Sagemcom S211 310 1319 Medium No 171 Robustel PI_XS20190426005ES 310 1321 Medium No 172 Britvic Britvic Demo 310 1327 Medium No 173 Amper AMPER 310 1339 Medium No 174 Hexing HXE110 310 1341 Medium No 175 Stalkit StalkIT 310 1345 Medium No 176 NetComm NTC100 310 1351 Medium No 177 Accent TRK-230 310 1353 Low No 178 Ublox Ublox SARA N310 310 1255 Low No 179 MuRata Type 1SS 310 1307 Low No 180 Teleoring RBMTX-Lite-NB 310 1317 Low No 181 Digital Keys LDK400 310 1333 Low No 182 Telefonica LWM2M BG96 310 1347 Very Low No 183 Ubicquia LCB200 310 1279 No 184 Britvic Tracker Barato 310 1291 No 185 Nokia GuardBand Nokia 310 1309 No 186 Mojio, N4T OBD Product 310 1311 No 233 TCL MKNO 310 431 Medium No 234 EFENTO Efento NBIoT Sensor 310 1241 High No 257 Contazara SmartNBIoT IPsec 310 1251 Medium No 259 Urbiotica SPM.1.1.1.0 310 1235 Very High Sí 260 Pietro Fiorentini RSE-N1 310 1233 Very High No 262 Teldat M8 Smart 310 1231 Very High No 264 ZIV 5CTB MCM 310 439 High No 265 Quectel BG600L-M3 310 1356 High No 288 General Mechatronics NaBi 310 309 Medium 289 Ublox Ublox R510S 310 310 Low 290 Exelonix VODEXL-01 310 311 Low 291 ETM ETM770-8 310 312 Low 292 Notioni MW29 310 313 Low 293 Efento Efento NBIoT Sensor_05.09 310 442 High 294 Inspiralia MySensor 310 445 Very High 295 Amper WM10003_V3.9 310 448 High 296 WeWins WeWins 5G 310 452 Low 297 Teltonika TRB141000 310 1237 Medium
Como podeis ver, se van ordenando correctamente, pero muestran todos los DeviceStatus.Id agrupados por MaxId. Lo que necesito también es mostrar el DeviceStatus.Status de ese MaxId que no se muestra.
Un saludo,
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 03/Septiembre/2020 a las 11:37 |
SELECT Devices.Id, Devices.Manufacturer, Devices.Model, Status.Id, Status.MaxId, Devices.Criticity, Devices.PIlot FROM Devices, (SELECT id, DeviceModel, Max(Id) AS MaxId FROM DeviceStatus GROUP BY DeviceModel, id) AS Status WHERE Devices.Model = Status.DeviceModel;
Prueba estq
|
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 03/Septiembre/2020 a las 11:56 |
Hola Guarracuco,
Aquí tienes el resultado:
Devices.Id Manufacturer Model Status.Id MaxId Criticity PIlot 264 ZIV 5CTB MCM 438 438 High No 264 ZIV 5CTB MCM 439 439 High No 173 Amper AMPER 1338 1338 Medium No 173 Amper AMPER 1339 1339 Medium No 96 Quectel BC92 407 407 Medium No 96 Quectel BC92 408 408 Medium No 96 Quectel BC92 409 409 Medium No 96 Quectel BC92 410 410 Medium No 96 Quectel BC92 411 411 Medium No 96 Quectel BC92 412 412 Medium No 96 Quectel BC92 413 413 Medium No 96 Quectel BC92 414 414 Medium No 96 Quectel BC92 415 415 Medium No 96 Quectel BC92 416 416 Medium No 96 Quectel BC92 417 417 Medium No 96 Quectel BC92 418 418 Medium No 96 Quectel BC92 419 419 Medium No 96 Quectel BC92 420 420 Medium No 265 Quectel BG600L-M3 432 432 High No 265 Quectel BG600L-M3 437 437 High No 265 Quectel BG600L-M3 1356 1356 High No 97 Quectel BG77 1244 1244 Medium No 97 Quectel BG77 1245 1245 Medium No 95 Quectel BG95-M3 1246 1246 Medium No 95 Quectel BG95-M3 1247 1247 Medium No 124 Amper BLN-1007nbLC 393 393 Medium Sí 124 Amper BLN-1007nbLC 394 394 Medium Sí 124 Amper BLN-1007nbLC 395 395 Medium Sí 124 Amper BLN-1007nbLC 396 396 Medium Sí 124 Amper BLN-1007nbLC 397 397 Medium Sí 124 Amper BLN-1007nbLC 398 398 Medium Sí 172 Britvic Britvic Demo 1326 1326 Medium No 172 Britvic Britvic Demo 1327 1327 Medium No 135 Conexiona Conexiona Wine Tracker 370 370 Low No 135 Conexiona Conexiona Wine Tracker 371 371 Low No 169 Contazara Contador Octave-Terranova 1314 1314 Medium No 169 Contazara Contador Octave-Terranova 1315 1315 Medium No 161 Contazara CZ4000_NB-IoT 1342 1342 Medium No 161 Contazara CZ4000_NB-IoT 1343 1343 Medium No 129 Inhemeter DDZ1513 335 335 Low No 129 Inhemeter DDZ1513 337 337 Low No 163 Ruptela Eco4 Light+ 2G 3G 1270 1270 Medium No 163 Ruptela Eco4 Light+ 2G 3G 1271 1271 Medium No 234 EFENTO Efento NBIoT Sensor 1240 1240 High No 234 EFENTO Efento NBIoT Sensor 1241 1241 High No 293 Efento Efento NBIoT Sensor_05.09 440 440 High 293 Efento Efento NBIoT Sensor_05.09 441 441 High 293 Efento Efento NBIoT Sensor_05.09 442 442 High 166 Sagemcom EG4EVO 1286 1286 Medium No 166 Sagemcom EG4EVO 1287 1287 Medium No 150 Theoremus eSLSF11 1280 1280 High Sí 150 Theoremus eSLSF11 1281 1281 High Sí 291 ETM ETM770-8 312 312 Low 122 Gemalto EXS82 402 402 Very Low No 120 CINTERION EXS82-W 381 381 Very Low No 128 MOBA FLS2 345 345 Low No 128 MOBA FLS2 346 346 Low No 128 MOBA FLS2 347 347 Low No 128 MOBA FLS2 348 348 Low No 128 MOBA FLS2 349 349 Low No 128 MOBA FLS2 350 350 Low No 155 Terranova Gas Meter en Terranova 1288 1288 High No 155 Terranova Gas Meter en Terranova 1289 1289 High No 165 Queclinck GL300ME 1282 1282 Medium No 165 Queclinck GL300ME 1283 1283 Medium No 126 Queclinck GL500M 317 317 Medium No 126 Queclinck GL500M 318 318 Medium No 126 Queclinck GL500M 320 320 Medium No 185 Nokia GuardBand Nokia 1308 1308 No 185 Nokia GuardBand Nokia 1309 1309 No 164 Queclinck GV350ME_1713 1272 1272 Medium No 164 Queclinck GV350ME_1713 1273 1273 Medium No 105 Queclicnk GV50ME 322 322 Medium No 104 Queclinck GV600ME 321 321 Medium No 136 Hongdian H7921-N 375 375 Low No 136 Hongdian H7921-N 376 376 Low No 136 Hongdian H7921-N 377 377 Low No 90 Humanox Humanox Soccer 1258 1258 Very High Sí 90 Humanox Humanox Soccer 1259 1259 Very High Sí 174 Hexing HXE110 1340 1340 Medium No 174 Hexing HXE110 1341 1341 Medium No 94 Amper ILK3000 338 338 Low No 94 Amper ILK3000 339 339 Low No 94 Amper ILK3000 340 340 Low No 94 Amper ILK3000 341 341 Low No 94 Amper ILK3000 342 342 Low No 94 Amper ILK3000 343 343 Low No 94 Amper ILK3000 344 344 Low No 152 FEN Inspiralia 1328 1328 High Sí 152 FEN Inspiralia 1329 1329 High Sí 107 Odins IPex12 400 400 Medium No 107 Odins IPex12 401 401 Medium No 134 GoldCard JGD1.6S-R 366 366 Medium No 134 GoldCard JGD1.6S-R 367 367 Medium No 114 Mobiletek L620 378 378 Low No 116 Mobiletek L630 380 380 Low No 115 Mobiletek L710 379 379 Low No 183 Ubicquia LCB200 1278 1278 No 183 Ubicquia LCB200 1279 1279 No 181 Digital Keys LDK400 1332 1332 Low No 181 Digital Keys LDK400 1333 1333 Low No 182 Telefonica LWM2M BG96 1346 1346 Very Low No 182 Telefonica LWM2M BG96 1347 1347 Very Low No 262 Teldat M8 Smart 1230 1230 Very High No 262 Teldat M8 Smart 1231 1231 Very High No 233 TCL MKNO 428 428 Medium No 233 TCL MKNO 429 429 Medium No 233 TCL MKNO 430 430 Medium No 233 TCL MKNO 431 431 Medium No 148 Matrix MTX-IOT 1260 1260 High Sí 148 Matrix MTX-IOT 1261 1261 High Sí 292 Notioni MW29 313 313 Low 294 Inspiralia MySensor 443 443 Very High 294 Inspiralia MySensor 444 444 Very High 294 Inspiralia MySensor 445 445 Very High 288 General Mechatronics NaBi 309 309 Medium 1 NWM NINGBO 1252 1252 High No 1 NWM NINGBO 1253 1253 High No 130 Nordic nRF9160 358 358 Very Low No 130 Nordic nRF9160 359 359 Very Low No 176 NetComm NTC100 1350 1350 Medium No 176 NetComm NTC100 1351 1351 Medium No 119 NetComm NTC-222 360 360 Very Low No 144 ZTE OBD Mojio 1322 1322 Very High No 144 ZTE OBD Mojio 1323 1323 Very High No 145 Net4Things OBD Movistar Car 1324 1324 Very High No 145 Net4Things OBD Movistar Car 1325 1325 Very High No 186 Mojio, N4T OBD Product 1310 1310 No 186 Mojio, N4T OBD Product 1311 1311 No 99 Oliver Oli 1248 1248 High Sí 99 Oliver Oli 1249 1249 High Sí 142 Open Innovation 2 go PB-SOUL1 1298 1298 Very High Sí 142 Open Innovation 2 go PB-SOUL1 1299 1299 Very High Sí 171 Robustel PI_XS20190426005ES 1320 1320 Medium No 171 Robustel PI_XS20190426005ES 1321 1321 Medium No 153 Datakorum PIPE21 1262 1262 High No 153 Datakorum PIPE21 1263 1263 High No 117 DataKorum PIPE22 361 361 Low No 121 Amper PO-170196 399 399 Very Low No 151 Molukas Quatro Sensor 1302 1302 High Sí 151 Molukas Quatro Sensor 1303 1303 High Sí 123 Inspiralia Quest3 - My Sensor 1238 1238 Very High Sí 123 Inspiralia Quest3 - My Sensor 1239 1239 Very High Sí 137 Vertigo Solutions Radarfy 368 368 Very Low No 137 Vertigo Solutions Radarfy 369 369 Very Low No 180 Teleoring RBMTX-Lite-NB 1316 1316 Low No 180 Teleoring RBMTX-Lite-NB 1317 1317 Low No 127 Multitech rCell 329 329 Low No 127 Multitech rCell 330 330 Low No 127 Multitech rCell 332 332 Low No 127 Multitech rCell 333 333 Low No 127 Multitech rCell 334 334 Low No 143 Panasonic Retro-fit NBIoT Meter IF 1354 1354 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF 1355 1355 Very High Sí 167 Hexing RPM Hexing BC95-G 1300 1300 Medium No 167 Hexing RPM Hexing BC95-G 1301 1301 Medium No 260 Pietro Fiorentini RSE-N1 1232 1232 Very High No 260 Pietro Fiorentini RSE-N1 1233 1233 Very High No 101 Teltonika RUT240 351 351 Low No 101 Teltonika RUT240 352 352 Low No 101 Teltonika RUT240 353 353 Low No 101 Teltonika RUT240 354 354 Low No 101 Teltonika RUT240 355 355 Low No 101 Teltonika RUT240 356 356 Low No 101 Teltonika RUT240 357 357 Low No 100 Teltonika RUT955 1242 1242 High No 100 Teltonika RUT955 1243 1243 High No 111 Teltonika RUTX110 423 423 Medium No 170 Sagemcom S211 1318 1318 Medium No 170 Sagemcom S211 1319 1319 Medium No 109 SAYME SAYME GATE 406 406 Medium No 149 SAYME SAYME-Dumpster 1268 1268 High Sí 149 SAYME SAYME-Dumpster 1269 1269 High Sí 91 WNC SKM-5xE50 403 403 Low No 91 WNC SKM-5xE50 404 404 Low No 91 WNC SKM-5xE50 405 405 Low No 125 Seowon SLM-120GI 324 324 Medium Sí 125 Seowon SLM-120GI 326 326 Medium Sí 125 Seowon SLM-120GI 327 327 Medium Sí 125 Seowon SLM-120GI 328 328 Medium Sí 156 Contazara Smart NBIOT Battery 1292 1292 High No 156 Contazara Smart NBIOT Battery 1293 1293 High No 158 Contazara Smart NBIoT BC68 1312 1312 High No 158 Contazara Smart NBIoT BC68 1313 1313 High No 139 Contazara Smart NBIoT BC68 A2.D9 1274 1274 Very High Sí 139 Contazara Smart NBIoT BC68 A2.D9 1275 1275 Very High Sí 154 Contazara Smart NBIoT BC68 A2.DD 1264 1264 High No 154 Contazara Smart NBIoT BC68 A2.DD 1265 1265 High No 157 Contazara Smart NBIoT BC68 B20 1294 1294 High No 157 Contazara Smart NBIoT BC68 B20 1295 1295 High No 141 Contazara Smart NBIoT BC68 B28 1296 1296 Very High Sí 141 Contazara Smart NBIoT BC68 B28 1297 1297 Very High Sí 257 Contazara SmartNBIoT IPsec 1250 1250 Medium No 257 Contazara SmartNBIoT IPsec 1251 1251 Medium No 160 Sayme SOS Button Sayme 1336 1336 High No 160 Sayme SOS Button Sayme 1337 1337 High No 259 Urbiotica SPM.1.1.1.0 1234 1234 Very High Sí 259 Urbiotica SPM.1.1.1.0 1235 1235 Very High Sí 2 Suntech ST4340 1256 1256 Medium No 2 Suntech ST4340 1257 1257 Medium No 175 Stalkit StalkIT 1344 1344 Medium No 175 Stalkit StalkIT 1345 1345 Medium No 131 StalKit Stalkit v2.5 382 382 High Sí 131 StalKit Stalkit v2.5 383 383 High Sí 131 StalKit Stalkit v2.5 384 384 High Sí 131 StalKit Stalkit v2.5 385 385 High Sí 131 StalKit Stalkit v2.5 386 386 High Sí 131 StalKit Stalkit v2.5 387 387 High Sí 131 StalKit Stalkit v2.5 388 388 High Sí 131 StalKit Stalkit v2.5 389 389 High Sí 131 StalKit Stalkit v2.5 390 390 High Sí 131 StalKit Stalkit v2.5 391 391 High Sí 112 Neoway T101 427 427 Medium No 138 Zebra TC57 372 372 Very Low No 138 Zebra TC57 373 373 Very Low No 138 Zebra TC57 374 374 Very Low No 103 Teldat Teldat H2 Automotive+ 1284 1284 Very Low No 103 Teldat Teldat H2 Automotive+ 1285 1285 Very Low No 146 Terranova Terranova Tests 1334 1334 Very High No 146 Terranova Terranova Tests 1335 1335 Very High No 133 Metersit TF19-002 364 364 Medium No 133 Metersit TF19-002 365 365 Medium No 162 Things Matrix Things Matrix big 1266 1266 Medium No 162 Things Matrix Things Matrix big 1267 1267 Medium No 113 Things Matrix Things Matrix small 323 323 Low No 184 Britvic Tracker Barato 1290 1290 No 184 Britvic Tracker Barato 1291 1291 No 297 Teltonika TRB141000 1236 1236 Medium 297 Teltonika TRB141000 1237 1237 Medium 177 Accent TRK-230 1352 1352 Low No 177 Accent TRK-230 1353 1353 Low No 147 Trackimo TRKM019 1348 1348 High No 147 Trackimo TRKM019 1349 1349 High No 110 Teltonika TRM25000 422 422 Medium No 179 MuRata Type 1SS 1306 1306 Low No 179 MuRata Type 1SS 1307 1307 Low No 289 Ublox Ublox R510S 310 310 Low 178 Ublox Ublox SARA N310 1254 1254 Low No 178 Ublox Ublox SARA N310 1255 1255 Low No 168 SmartMeterQ UGM v2 NBIoT 1304 1304 Medium No 168 SmartMeterQ UGM v2 NBIoT 1305 1305 Medium No 159 SmartMeterQ UGM-G4 1330 1330 High No 159 SmartMeterQ UGM-G4 1331 1331 High No 290 Exelonix VODEXL-01 311 311 Low 296 WeWins WeWins 5G 449 449 Low 296 WeWins WeWins 5G 450 450 Low 296 WeWins WeWins 5G 451 451 Low 296 WeWins WeWins 5G 452 452 Low 106 WeWins WeWins OBD 392 392 Medium No 118 Wibeee Wibeee-Box 315 315 Very Low No 295 Amper WM10003_V3.9 424 424 High 295 Amper WM10003_V3.9 425 425 High 295 Amper WM10003_V3.9 426 426 High 295 Amper WM10003_V3.9 446 446 High 295 Amper WM10003_V3.9 447 447 High 295 Amper WM10003_V3.9 448 448 High 140 Amper WM1000S 1276 1276 Very High Sí 140 Amper WM1000S 1277 1277 Very High Sí
Realmente ahora no muestra lo anterior, pero tampoco lo deseado...
Un saludo,
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 03/Septiembre/2020 a las 13:19 |
EEn la sub consulta, luego del select, coloca Distintc.
|
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 03/Septiembre/2020 a las 13:27 |
Hola Guarracuco,
Sigue sin funcionar del todo... Devices.Id Manufacturer Model Status.Id MaxId Criticity PIlot 288 General Mechatronics NaBi 309 309 Medium 289 Ublox Ublox R510S 310 310 Low 290 Exelonix VODEXL-01 311 311 Low 291 ETM ETM770-8 312 312 Low 292 Notioni MW29 313 313 Low 118 Wibeee Wibeee-Box 315 315 Very Low No 126 Queclinck GL500M 317 317 Medium No 126 Queclinck GL500M 318 318 Medium No 126 Queclinck GL500M 320 320 Medium No 104 Queclinck GV600ME 321 321 Medium No 105 Queclicnk GV50ME 322 322 Medium No 113 Things Matrix Things Matrix small 323 323 Low No 125 Seowon SLM-120GI 324 324 Medium Sí 125 Seowon SLM-120GI 326 326 Medium Sí 125 Seowon SLM-120GI 327 327 Medium Sí 125 Seowon SLM-120GI 328 328 Medium Sí 127 Multitech rCell 329 329 Low No 127 Multitech rCell 330 330 Low No 127 Multitech rCell 332 332 Low No 127 Multitech rCell 333 333 Low No 127 Multitech rCell 334 334 Low No 129 Inhemeter DDZ1513 335 335 Low No 129 Inhemeter DDZ1513 337 337 Low No 94 Amper ILK3000 338 338 Low No 94 Amper ILK3000 339 339 Low No 94 Amper ILK3000 340 340 Low No 94 Amper ILK3000 341 341 Low No 94 Amper ILK3000 342 342 Low No 94 Amper ILK3000 343 343 Low No 94 Amper ILK3000 344 344 Low No 128 MOBA FLS2 345 345 Low No 128 MOBA FLS2 346 346 Low No 128 MOBA FLS2 347 347 Low No 128 MOBA FLS2 348 348 Low No 128 MOBA FLS2 349 349 Low No 128 MOBA FLS2 350 350 Low No 101 Teltonika RUT240 351 351 Low No 101 Teltonika RUT240 352 352 Low No 101 Teltonika RUT240 353 353 Low No 101 Teltonika RUT240 354 354 Low No 101 Teltonika RUT240 355 355 Low No 101 Teltonika RUT240 356 356 Low No 101 Teltonika RUT240 357 357 Low No 130 Nordic nRF9160 358 358 Very Low No 130 Nordic nRF9160 359 359 Very Low No 119 NetComm NTC-222 360 360 Very Low No 117 DataKorum PIPE22 361 361 Low No 133 Metersit TF19-002 364 364 Medium No 133 Metersit TF19-002 365 365 Medium No 134 GoldCard JGD1.6S-R 366 366 Medium No 134 GoldCard JGD1.6S-R 367 367 Medium No 137 Vertigo Solutions Radarfy 368 368 Very Low No 137 Vertigo Solutions Radarfy 369 369 Very Low No 135 Conexiona Conexiona Wine Tracker 370 370 Low No 135 Conexiona Conexiona Wine Tracker 371 371 Low No 138 Zebra TC57 372 372 Very Low No 138 Zebra TC57 373 373 Very Low No 138 Zebra TC57 374 374 Very Low No 136 Hongdian H7921-N 375 375 Low No 136 Hongdian H7921-N 376 376 Low No 136 Hongdian H7921-N 377 377 Low No 114 Mobiletek L620 378 378 Low No 115 Mobiletek L710 379 379 Low No 116 Mobiletek L630 380 380 Low No 120 CINTERION EXS82-W 381 381 Very Low No 131 StalKit Stalkit v2.5 382 382 High Sí 131 StalKit Stalkit v2.5 383 383 High Sí 131 StalKit Stalkit v2.5 384 384 High Sí 131 StalKit Stalkit v2.5 385 385 High Sí 131 StalKit Stalkit v2.5 386 386 High Sí 131 StalKit Stalkit v2.5 387 387 High Sí 131 StalKit Stalkit v2.5 388 388 High Sí 131 StalKit Stalkit v2.5 389 389 High Sí 131 StalKit Stalkit v2.5 390 390 High Sí 131 StalKit Stalkit v2.5 391 391 High Sí 106 WeWins WeWins OBD 392 392 Medium No 124 Amper BLN-1007nbLC 393 393 Medium Sí 124 Amper BLN-1007nbLC 394 394 Medium Sí 124 Amper BLN-1007nbLC 395 395 Medium Sí 124 Amper BLN-1007nbLC 396 396 Medium Sí 124 Amper BLN-1007nbLC 397 397 Medium Sí 124 Amper BLN-1007nbLC 398 398 Medium Sí 121 Amper PO-170196 399 399 Very Low No 107 Odins IPex12 400 400 Medium No 107 Odins IPex12 401 401 Medium No 122 Gemalto EXS82 402 402 Very Low No 91 WNC SKM-5xE50 403 403 Low No 91 WNC SKM-5xE50 404 404 Low No 91 WNC SKM-5xE50 405 405 Low No 109 SAYME SAYME GATE 406 406 Medium No 96 Quectel BC92 407 407 Medium No 96 Quectel BC92 408 408 Medium No 96 Quectel BC92 409 409 Medium No 96 Quectel BC92 410 410 Medium No 96 Quectel BC92 411 411 Medium No 96 Quectel BC92 412 412 Medium No 96 Quectel BC92 413 413 Medium No 96 Quectel BC92 414 414 Medium No 96 Quectel BC92 415 415 Medium No 96 Quectel BC92 416 416 Medium No 96 Quectel BC92 417 417 Medium No 96 Quectel BC92 418 418 Medium No 96 Quectel BC92 419 419 Medium No 96 Quectel BC92 420 420 Medium No 110 Teltonika TRM25000 422 422 Medium No 111 Teltonika RUTX110 423 423 Medium No 295 Amper WM10003_V3.9 424 424 High 295 Amper WM10003_V3.9 425 425 High 295 Amper WM10003_V3.9 426 426 High 112 Neoway T101 427 427 Medium No 233 TCL MKNO 428 428 Medium No 233 TCL MKNO 429 429 Medium No 233 TCL MKNO 430 430 Medium No 233 TCL MKNO 431 431 Medium No 265 Quectel BG600L-M3 432 432 High No 265 Quectel BG600L-M3 437 437 High No 264 ZIV 5CTB MCM 438 438 High No 264 ZIV 5CTB MCM 439 439 High No 293 Efento Efento NBIoT Sensor_05.09 440 440 High 293 Efento Efento NBIoT Sensor_05.09 441 441 High 293 Efento Efento NBIoT Sensor_05.09 442 442 High 294 Inspiralia MySensor 443 443 Very High 294 Inspiralia MySensor 444 444 Very High 294 Inspiralia MySensor 445 445 Very High 295 Amper WM10003_V3.9 446 446 High 295 Amper WM10003_V3.9 447 447 High 295 Amper WM10003_V3.9 448 448 High 296 WeWins WeWins 5G 449 449 Low 296 WeWins WeWins 5G 450 450 Low 296 WeWins WeWins 5G 451 451 Low 296 WeWins WeWins 5G 452 452 Low 262 Teldat M8 Smart 1230 1230 Very High No 262 Teldat M8 Smart 1231 1231 Very High No 260 Pietro Fiorentini RSE-N1 1232 1232 Very High No 260 Pietro Fiorentini RSE-N1 1233 1233 Very High No 259 Urbiotica SPM.1.1.1.0 1234 1234 Very High Sí 259 Urbiotica SPM.1.1.1.0 1235 1235 Very High Sí 297 Teltonika TRB141000 1236 1236 Medium 297 Teltonika TRB141000 1237 1237 Medium 123 Inspiralia Quest3 - My Sensor 1238 1238 Very High Sí 123 Inspiralia Quest3 - My Sensor 1239 1239 Very High Sí 234 EFENTO Efento NBIoT Sensor 1240 1240 High No 234 EFENTO Efento NBIoT Sensor 1241 1241 High No 100 Teltonika RUT955 1242 1242 High No 100 Teltonika RUT955 1243 1243 High No 97 Quectel BG77 1244 1244 Medium No 97 Quectel BG77 1245 1245 Medium No 95 Quectel BG95-M3 1246 1246 Medium No 95 Quectel BG95-M3 1247 1247 Medium No 99 Oliver Oli 1248 1248 High Sí 99 Oliver Oli 1249 1249 High Sí 257 Contazara SmartNBIoT IPsec 1250 1250 Medium No 257 Contazara SmartNBIoT IPsec 1251 1251 Medium No 1 NWM NINGBO 1252 1252 High No 1 NWM NINGBO 1253 1253 High No 178 Ublox Ublox SARA N310 1254 1254 Low No 178 Ublox Ublox SARA N310 1255 1255 Low No 2 Suntech ST4340 1256 1256 Medium No 2 Suntech ST4340 1257 1257 Medium No 90 Humanox Humanox Soccer 1258 1258 Very High Sí 90 Humanox Humanox Soccer 1259 1259 Very High Sí 148 Matrix MTX-IOT 1260 1260 High Sí 148 Matrix MTX-IOT 1261 1261 High Sí 153 Datakorum PIPE21 1262 1262 High No 153 Datakorum PIPE21 1263 1263 High No 154 Contazara Smart NBIoT BC68 A2.DD 1264 1264 High No 154 Contazara Smart NBIoT BC68 A2.DD 1265 1265 High No 162 Things Matrix Things Matrix big 1266 1266 Medium No 162 Things Matrix Things Matrix big 1267 1267 Medium No 149 SAYME SAYME-Dumpster 1268 1268 High Sí 149 SAYME SAYME-Dumpster 1269 1269 High Sí 163 Ruptela Eco4 Light+ 2G 3G 1270 1270 Medium No 163 Ruptela Eco4 Light+ 2G 3G 1271 1271 Medium No 164 Queclinck GV350ME_1713 1272 1272 Medium No 164 Queclinck GV350ME_1713 1273 1273 Medium No 139 Contazara Smart NBIoT BC68 A2.D9 1274 1274 Very High Sí 139 Contazara Smart NBIoT BC68 A2.D9 1275 1275 Very High Sí 140 Amper WM1000S 1276 1276 Very High Sí 140 Amper WM1000S 1277 1277 Very High Sí 183 Ubicquia LCB200 1278 1278 No 183 Ubicquia LCB200 1279 1279 No 150 Theoremus eSLSF11 1280 1280 High Sí 150 Theoremus eSLSF11 1281 1281 High Sí 165 Queclinck GL300ME 1282 1282 Medium No 165 Queclinck GL300ME 1283 1283 Medium No 103 Teldat Teldat H2 Automotive+ 1284 1284 Very Low No 103 Teldat Teldat H2 Automotive+ 1285 1285 Very Low No 166 Sagemcom EG4EVO 1286 1286 Medium No 166 Sagemcom EG4EVO 1287 1287 Medium No 155 Terranova Gas Meter en Terranova 1288 1288 High No 155 Terranova Gas Meter en Terranova 1289 1289 High No 184 Britvic Tracker Barato 1290 1290 No 184 Britvic Tracker Barato 1291 1291 No 156 Contazara Smart NBIOT Battery 1292 1292 High No 156 Contazara Smart NBIOT Battery 1293 1293 High No 157 Contazara Smart NBIoT BC68 B20 1294 1294 High No 157 Contazara Smart NBIoT BC68 B20 1295 1295 High No 141 Contazara Smart NBIoT BC68 B28 1296 1296 Very High Sí 141 Contazara Smart NBIoT BC68 B28 1297 1297 Very High Sí 142 Open Innovation 2 go PB-SOUL1 1298 1298 Very High Sí 142 Open Innovation 2 go PB-SOUL1 1299 1299 Very High Sí 167 Hexing RPM Hexing BC95-G 1300 1300 Medium No 167 Hexing RPM Hexing BC95-G 1301 1301 Medium No 151 Molukas Quatro Sensor 1302 1302 High Sí 151 Molukas Quatro Sensor 1303 1303 High Sí 168 SmartMeterQ UGM v2 NBIoT 1304 1304 Medium No 168 SmartMeterQ UGM v2 NBIoT 1305 1305 Medium No 179 MuRata Type 1SS 1306 1306 Low No 179 MuRata Type 1SS 1307 1307 Low No 185 Nokia GuardBand Nokia 1308 1308 No 185 Nokia GuardBand Nokia 1309 1309 No 186 Mojio, N4T OBD Product 1310 1310 No 186 Mojio, N4T OBD Product 1311 1311 No 158 Contazara Smart NBIoT BC68 1312 1312 High No 158 Contazara Smart NBIoT BC68 1313 1313 High No 169 Contazara Contador Octave-Terranova 1314 1314 Medium No 169 Contazara Contador Octave-Terranova 1315 1315 Medium No 180 Teleoring RBMTX-Lite-NB 1316 1316 Low No 180 Teleoring RBMTX-Lite-NB 1317 1317 Low No 170 Sagemcom S211 1318 1318 Medium No 170 Sagemcom S211 1319 1319 Medium No 171 Robustel PI_XS20190426005ES 1320 1320 Medium No 171 Robustel PI_XS20190426005ES 1321 1321 Medium No 144 ZTE OBD Mojio 1322 1322 Very High No 144 ZTE OBD Mojio 1323 1323 Very High No 145 Net4Things OBD Movistar Car 1324 1324 Very High No 145 Net4Things OBD Movistar Car 1325 1325 Very High No 172 Britvic Britvic Demo 1326 1326 Medium No 172 Britvic Britvic Demo 1327 1327 Medium No 152 FEN Inspiralia 1328 1328 High Sí 152 FEN Inspiralia 1329 1329 High Sí 159 SmartMeterQ UGM-G4 1330 1330 High No 159 SmartMeterQ UGM-G4 1331 1331 High No 181 Digital Keys LDK400 1332 1332 Low No 181 Digital Keys LDK400 1333 1333 Low No 146 Terranova Terranova Tests 1334 1334 Very High No 146 Terranova Terranova Tests 1335 1335 Very High No 160 Sayme SOS Button Sayme 1336 1336 High No 160 Sayme SOS Button Sayme 1337 1337 High No 173 Amper AMPER 1338 1338 Medium No 173 Amper AMPER 1339 1339 Medium No 174 Hexing HXE110 1340 1340 Medium No 174 Hexing HXE110 1341 1341 Medium No 161 Contazara CZ4000_NB-IoT 1342 1342 Medium No 161 Contazara CZ4000_NB-IoT 1343 1343 Medium No 175 Stalkit StalkIT 1344 1344 Medium No 175 Stalkit StalkIT 1345 1345 Medium No 182 Telefonica LWM2M BG96 1346 1346 Very Low No 182 Telefonica LWM2M BG96 1347 1347 Very Low No 147 Trackimo TRKM019 1348 1348 High No 147 Trackimo TRKM019 1349 1349 High No 176 NetComm NTC100 1350 1350 Medium No 176 NetComm NTC100 1351 1351 Medium No 177 Accent TRK-230 1352 1352 Low No 177 Accent TRK-230 1353 1353 Low No 143 Panasonic Retro-fit NBIoT Meter IF 1354 1354 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF 1355 1355 Very High Sí 265 Quectel BG600L-M3 1356 1356 High No
Es posible que no este funcionando correctamente la sentencia Max? No lo ordena por el mayor de cada dispositivo...
Aquí la sentencia:
SELECT Devices.Id, Devices.Manufacturer, Devices.Model, Status.Id, Status.MaxId, Devices.Criticity, Devices.PIlot FROM Devices, (SELECT Distinct id, DeviceModel, Max(Id) AS MaxId FROM DeviceStatus GROUP BY DeviceModel, id) AS Status WHERE Devices.Model = Status.DeviceModel;
Un saludo,
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 03/Septiembre/2020 a las 16:35 |
Hoy no puedo ayudarte. Me temía que al agregar la columna ID, mostrará otros resultados. Déjala como estaba, es decir remueve la instruccion distinta, la columna id, y crea otra subquery que crrecupera la columna que deseas.
|
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 04/Septiembre/2020 a las 13:44 |
Hola,
Volví a hacerlo e investigar con otras sentencias, pero sigo sin poder ver algo parecido a lo que quiero...
¿Alguna idea?
Un saludo
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 04/Septiembre/2020 a las 14:49 |
Hola, tengo un par de horas para dedicarme a esta query. Vamos a ver como lograrlo.
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 04/Septiembre/2020 a las 15:13 |
Prueba esta. Recuerda que lo estoy haciendo a ciegas, ya que no tengo esa estructura de datos. La idea es obtener el valor de la columna status, utilizando el maxid de la subconsulta origen de datos.
SELECT Devices.Id, Devices.Manufacturer, Devices.Model, (select status from DeviceStatus where id = Status.MaxId) as DevStatus, Status.MaxId, Devices.Criticity, Devices.PIlot FROM Devices, DeviceStatus, (SELECT DeviceModel, Max(Id) AS MaxId FROM DeviceStatus GROUP BY DeviceModel) AS Status WHERE Devices.Model = Status.DeviceModel;
|
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 07/Septiembre/2020 a las 09:04 |
Hola Guarracuco,
Gracias por la pronta respuesta.
En cuanto a la Query, lo ordena adecuadamente pero lo sigue realizando por bloques (2 primeros bloques):
Id Manufacturer Model DevStatus MaxId Criticity PIlot 1 NWM NINGBO Finished 1253 High No 2 Suntech ST4340 Finished 1257 Medium No 90 Humanox Humanox Soccer Finished 1259 Very High Sí 91 WNC SKM-5xE50 3 405 Low No 94 Amper ILK3000 3 344 Low No 95 Quectel BG95-M3 Finished 1247 Medium No 96 Quectel BC92 2 420 Medium No 97 Quectel BG77 Finished 1245 Medium No 99 Oliver Oli Finished 1249 High Sí 100 Teltonika RUT955 Finished 1243 High No 101 Teltonika RUT240 2 357 Low No 103 Teldat Teldat H2 Automotive+ Finished 1285 Very Low No 104 Queclinck GV600ME 1 321 Medium No 105 Queclicnk GV50ME 1 322 Medium No 106 WeWins WeWins OBD 1 392 Medium No 107 Odins IPex12 1 401 Medium No 109 SAYME SAYME GATE 1 406 Medium No 110 Teltonika TRM25000 1 422 Medium No 111 Teltonika RUTX110 1 423 Medium No 112 Neoway T101 1 427 Medium No 113 Things Matrix Things Matrix small 1 323 Low No 114 Mobiletek L620 1 378 Low No 115 Mobiletek L710 1 379 Low No 116 Mobiletek L630 1 380 Low No 117 DataKorum PIPE22 1 361 Low No 118 Wibeee Wibeee-Box 1 315 Very Low No 119 NetComm NTC-222 1 360 Very Low No 120 CINTERION EXS82-W 1 381 Very Low No 121 Amper PO-170196 1 399 Very Low No 122 Gemalto EXS82 1 402 Very Low No 123 Inspiralia Quest3 - My Sensor Finished 1239 Very High Sí 124 Amper BLN-1007nbLC 3 398 Medium Sí 125 Seowon SLM-120GI 2 328 Medium Sí 126 Queclinck GL500M 3 320 Medium No 127 Multitech rCell 3 334 Low No 128 MOBA FLS2 3 350 Low No 129 Inhemeter DDZ1513 3 337 Low No 130 Nordic nRF9160 3 359 Very Low No 131 StalKit Stalkit v2.5 4 391 High Sí 133 Metersit TF19-002 4 365 Medium No 134 GoldCard JGD1.6S-R 4 367 Medium No 135 Conexiona Conexiona Wine Tracker 4 371 Low No 136 Hongdian H7921-N 4 377 Low No 137 Vertigo Solutions Radarfy 4 369 Very Low No 138 Zebra TC57 4 374 Very Low No 139 Contazara Smart NBIoT BC68 A2.D9 Finished 1275 Very High Sí 140 Amper WM1000S Finished 1277 Very High Sí 141 Contazara Smart NBIoT BC68 B28 Finished 1297 Very High Sí 142 Open Innovation 2 go PB-SOUL1 Finished 1299 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF Finished 1355 Very High Sí 144 ZTE OBD Mojio Finished 1323 Very High No 145 Net4Things OBD Movistar Car Finished 1325 Very High No 146 Terranova Terranova Tests Finished 1335 Very High No 147 Trackimo TRKM019 Finished 1349 High No 148 Matrix MTX-IOT Finished 1261 High Sí 149 SAYME SAYME-Dumpster Finished 1269 High Sí 150 Theoremus eSLSF11 Finished 1281 High Sí 151 Molukas Quatro Sensor Finished 1303 High Sí 152 FEN Inspiralia Finished 1329 High Sí 153 Datakorum PIPE21 Finished 1263 High No 154 Contazara Smart NBIoT BC68 A2.DD Finished 1265 High No 155 Terranova Gas Meter en Terranova Finished 1289 High No 156 Contazara Smart NBIOT Battery Finished 1293 High No 157 Contazara Smart NBIoT BC68 B20 Finished 1295 High No 158 Contazara Smart NBIoT BC68 Finished 1313 High No 159 SmartMeterQ UGM-G4 Finished 1331 High No 160 Sayme SOS Button Sayme Finished 1337 High No 161 Contazara CZ4000_NB-IoT Finished 1343 Medium No 162 Things Matrix Things Matrix big Finished 1267 Medium No 163 Ruptela Eco4 Light+ 2G 3G Finished 1271 Medium No 164 Queclinck GV350ME_1713 Finished 1273 Medium No 165 Queclinck GL300ME Finished 1283 Medium No 166 Sagemcom EG4EVO Finished 1287 Medium No 167 Hexing RPM Hexing BC95-G Finished 1301 Medium No 168 SmartMeterQ UGM v2 NBIoT Finished 1305 Medium No 169 Contazara Contador Octave-Terranova Finished 1315 Medium No 170 Sagemcom S211 Finished 1319 Medium No 171 Robustel PI_XS20190426005ES Finished 1321 Medium No 172 Britvic Britvic Demo Finished 1327 Medium No 173 Amper AMPER Finished 1339 Medium No 174 Hexing HXE110 Finished 1341 Medium No 175 Stalkit StalkIT Finished 1345 Medium No 176 NetComm NTC100 Finished 1351 Medium No 177 Accent TRK-230 Finished 1353 Low No 178 Ublox Ublox SARA N310 Finished 1255 Low No 179 MuRata Type 1SS Finished 1307 Low No 180 Teleoring RBMTX-Lite-NB Finished 1317 Low No 181 Digital Keys LDK400 Finished 1333 Low No 182 Telefonica LWM2M BG96 Finished 1347 Very Low No 183 Ubicquia LCB200 Finished 1279 No 184 Britvic Tracker Barato Finished 1291 No 185 Nokia GuardBand Nokia Finished 1309 No 186 Mojio, N4T OBD Product Finished 1311 No 233 TCL MKNO 4 431 Medium No 234 EFENTO Efento NBIoT Sensor Finished 1241 High No 257 Contazara SmartNBIoT IPsec Finished 1251 Medium No 259 Urbiotica SPM.1.1.1.0 Finished 1235 Very High Sí 260 Pietro Fiorentini RSE-N1 Finished 1233 Very High No 262 Teldat M8 Smart Finished 1231 Very High No 264 ZIV 5CTB MCM Waiting 439 High No 265 Quectel BG600L-M3 Testing 1356 High No 288 General Mechatronics NaBi 1 309 Medium 289 Ublox Ublox R510S Created 310 Low 290 Exelonix VODEXL-01 Created 311 Low 291 ETM ETM770-8 Created 312 Low 292 Notioni MW29 Created 313 Low 293 Efento Efento NBIoT Sensor_05.09 Waiting 442 High 294 Inspiralia MySensor Waiting 445 Very High 295 Amper WM10003_V3.9 Waiting 448 High 296 WeWins WeWins 5G Finished 452 Low 297 Teltonika TRB141000 Finished 1237 Medium 1 NWM NINGBO Finished 1253 High No 2 Suntech ST4340 Finished 1257 Medium No 90 Humanox Humanox Soccer Finished 1259 Very High Sí 91 WNC SKM-5xE50 3 405 Low No 94 Amper ILK3000 3 344 Low No 95 Quectel BG95-M3 Finished 1247 Medium No 96 Quectel BC92 2 420 Medium No 97 Quectel BG77 Finished 1245 Medium No 99 Oliver Oli Finished 1249 High Sí 100 Teltonika RUT955 Finished 1243 High No 101 Teltonika RUT240 2 357 Low No 103 Teldat Teldat H2 Automotive+ Finished 1285 Very Low No 104 Queclinck GV600ME 1 321 Medium No 105 Queclicnk GV50ME 1 322 Medium No 106 WeWins WeWins OBD 1 392 Medium No 107 Odins IPex12 1 401 Medium No 109 SAYME SAYME GATE 1 406 Medium No 110 Teltonika TRM25000 1 422 Medium No 111 Teltonika RUTX110 1 423 Medium No 112 Neoway T101 1 427 Medium No 113 Things Matrix Things Matrix small 1 323 Low No 114 Mobiletek L620 1 378 Low No 115 Mobiletek L710 1 379 Low No 116 Mobiletek L630 1 380 Low No 117 DataKorum PIPE22 1 361 Low No 118 Wibeee Wibeee-Box 1 315 Very Low No 119 NetComm NTC-222 1 360 Very Low No 120 CINTERION EXS82-W 1 381 Very Low No 121 Amper PO-170196 1 399 Very Low No 122 Gemalto EXS82 1 402 Very Low No 123 Inspiralia Quest3 - My Sensor Finished 1239 Very High Sí 124 Amper BLN-1007nbLC 3 398 Medium Sí 125 Seowon SLM-120GI 2 328 Medium Sí 126 Queclinck GL500M 3 320 Medium No 127 Multitech rCell 3 334 Low No 128 MOBA FLS2 3 350 Low No 129 Inhemeter DDZ1513 3 337 Low No 130 Nordic nRF9160 3 359 Very Low No 131 StalKit Stalkit v2.5 4 391 High Sí 133 Metersit TF19-002 4 365 Medium No 134 GoldCard JGD1.6S-R 4 367 Medium No 135 Conexiona Conexiona Wine Tracker 4 371 Low No 136 Hongdian H7921-N 4 377 Low No 137 Vertigo Solutions Radarfy 4 369 Very Low No 138 Zebra TC57 4 374 Very Low No 139 Contazara Smart NBIoT BC68 A2.D9 Finished 1275 Very High Sí 140 Amper WM1000S Finished 1277 Very High Sí 141 Contazara Smart NBIoT BC68 B28 Finished 1297 Very High Sí 142 Open Innovation 2 go PB-SOUL1 Finished 1299 Very High Sí 143 Panasonic Retro-fit NBIoT Meter IF Finished 1355 Very High Sí 144 ZTE OBD Mojio Finished 1323 Very High No 145 Net4Things OBD Movistar Car Finished 1325 Very High No 146 Terranova Terranova Tests Finished 1335 Very High No 147 Trackimo TRKM019 Finished 1349 High No 148 Matrix MTX-IOT Finished 1261 High Sí 149 SAYME SAYME-Dumpster Finished 1269 High Sí 150 Theoremus eSLSF11 Finished 1281 High Sí 151 Molukas Quatro Sensor Finished 1303 High Sí 152 FEN Inspiralia Finished 1329 High Sí 153 Datakorum PIPE21 Finished 1263 High No 154 Contazara Smart NBIoT BC68 A2.DD Finished 1265 High No 155 Terranova Gas Meter en Terranova Finished 1289 High No 156 Contazara Smart NBIOT Battery Finished 1293 High No 157 Contazara Smart NBIoT BC68 B20 Finished 1295 High No 158 Contazara Smart NBIoT BC68 Finished 1313 High No 159 SmartMeterQ UGM-G4 Finished 1331 High No 160 Sayme SOS Button Sayme Finished 1337 High No 161 Contazara CZ4000_NB-IoT Finished 1343 Medium No 162 Things Matrix Things Matrix big Finished 1267 Medium No 163 Ruptela Eco4 Light+ 2G 3G Finished 1271 Medium No 164 Queclinck GV350ME_1713 Finished 1273 Medium No 165 Queclinck GL300ME Finished 1283 Medium No 166 Sagemcom EG4EVO Finished 1287 Medium No 167 Hexing RPM Hexing BC95-G Finished 1301 Medium No 168 SmartMeterQ UGM v2 NBIoT Finished 1305 Medium No 169 Contazara Contador Octave-Terranova Finished 1315 Medium No 170 Sagemcom S211 Finished 1319 Medium No 171 Robustel PI_XS20190426005ES Finished 1321 Medium No 172 Britvic Britvic Demo Finished 1327 Medium No 173 Amper AMPER Finished 1339 Medium No 174 Hexing HXE110 Finished 1341 Medium No 175 Stalkit StalkIT Finished 1345 Medium No 176 NetComm NTC100 Finished 1351 Medium No 177 Accent TRK-230 Finished 1353 Low No 178 Ublox Ublox SARA N310 Finished 1255 Low No 179 MuRata Type 1SS Finished 1307 Low No 180 Teleoring RBMTX-Lite-NB Finished 1317 Low No 181 Digital Keys LDK400 Finished 1333 Low No 182 Telefonica LWM2M BG96 Finished 1347 Very Low No 183 Ubicquia LCB200 Finished 1279 No 184 Britvic Tracker Barato Finished 1291 No 185 Nokia GuardBand Nokia Finished 1309 No 186 Mojio, N4T OBD Product Finished 1311 No 233 TCL MKNO 4 431 Medium No 234 EFENTO Efento NBIoT Sensor Finished 1241 High No 257 Contazara SmartNBIoT IPsec Finished 1251 Medium No 259 Urbiotica SPM.1.1.1.0 Finished 1235 Very High Sí 260 Pietro Fiorentini RSE-N1 Finished 1233 Very High No 262 Teldat M8 Smart Finished 1231 Very High No 264 ZIV 5CTB MCM Waiting 439 High No 265 Quectel BG600L-M3 Testing 1356 High No 288 General Mechatronics NaBi 1 309 Medium 289 Ublox Ublox R510S Created 310 Low 290 Exelonix VODEXL-01 Created 311 Low 291 ETM ETM770-8 Created 312 Low 292 Notioni MW29 Created 313 Low 293 Efento Efento NBIoT Sensor_05.09 Waiting 442 High 294 Inspiralia MySensor Waiting 445 Very High 295 Amper WM10003_V3.9 Waiting 448 High 296 WeWins WeWins 5G Finished 452 Low 297 Teltonika TRB141000 Finished 1237 Medium
Creo que solo falta que solo se quede con el primer bloque.
Un saludo,
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 07/Septiembre/2020 a las 13:00 |
En el segundo FROM,remueve DeviceStatus
|
|
guarracuco
Moderador
Unido: 24/Abril/2004
Localización: EEUU
Estado: Sin conexión
Puntos: 3239
|
Enviado: 07/Septiembre/2020 a las 13:25 |
Hay varias maneras de lograr el conjunto de registros que deseas. Esta segunda consulta, debería ser más rápido ya que no hay subconsultas. Escrito al vuelo en el mobil, pretendo explicar otra forma de hacerlo. La idea principal es obtener el máximo ID por cada registro.
Select Max(Id) From DeviceStatus
Creamos la consulta, donde aplicaremos la primera como criterio
Select D.id, D.Manufacturer, D.Model, S.status, D.Criticity, D.Pilot From Devices D Inner Join DeviceStatus S ON D.Model = S.DeviceModel Where D.Id in (select Max(Id) From DeviceStatus)
|
|
shenares
Habitual
Unido: 01/Septiembre/2020
Localización: Madrid
Estado: Sin conexión
Puntos: 62
|
Enviado: 07/Septiembre/2020 a las 14:19 |
Hola Guarracuco,
La sentencia funciona perfectamente!!
Muchisimas gracias por la ayuda!
Un saludo,
|
|