Converting mysql query to mssql query for sql server 2000

Question

I was stuck figuring out why there's error when I execute the mysql query on mssql query. I was using sql server 2000. My goal is to achieve the result with the same way that I use on mysql.

A little explanation about the database: The database is about a gps tracker with 3 main table: incoming, md_login, and master_device.

Here is the table and the structure that I will give:

Structure of table incoming:
This table mainly used for the incoming data of the gps tracker for each vehicle, so every interval there'll be incoming data into this tables. Regarding about some of the table structure, you can say 'tanggal' is the meaning of 'date' in English

Text1 varchar
...
Text18 varchar <used as imei>
...
Text31 varchar
Distance varchar
Tanggal datetime
TanggalIncoming datetime 
StartDate datetime
EndDate datetime
EngineStatus varchar
AccStatus varchar
Moving varchar
Address varchar

---

Structure of table md_login:
This table used to store the vehicle with the imei data, so 1 Log_ID can have many Log_DeviceID.

Log_ID char <used as username>
Log_DeviceID varchar <used as vehicle number> 
Log_DeviceIMEI varchar <used as imei>
Log_Date datetime

Sample data of table md_login:

Log_ID - Alex
Log_DeviceID - B 7777 GHI
Log_DeviceImei - 012896001194123
Log_Date - 2017-05-30 13:46:57

---

Structure of table master_device:

Device_Imei varchar
Device_PoliceNumber char
Device_MobileNumber char
Device_MobileNumber2 char
Model varchar
Port char
PortDevice char
ActiveDate datetime
LastUpdate datetime
IdxConn varchar
CommandOperate char
Picture varchar

Sample data of table master_device:

Device_Imei - 012896001194123
Device_PoliceNumber - B 7777 GHI
Device_MobileNumber - 01234567
Device_MobileNumber2 - 
Model - STV-08
Port - 340
PortDevice - 20557
ActiveDate - 2017-05-30 13:46:57
LastUpdate - Null
IdxConn - Null
CommandOperate - Null
Picture - livina_grey.png

---

Here's the query that already works on mysql:

SELECT fi.text18 as Imei,
        md.Device_PoliceNumber,
        fi.Text6 as Lat,
        fi.Text8 as Lng,
        fi.Text10 as Speed,
        fi.Text16 as Gps_Signal,
        fi.Text21 as Battery,
        fi.Text22 as Charging,
        fi.Text29 as Oil,
        fi.Text30 as Temperature,
        md.Picture,
        fi.EngineStatus,
       fi.TanggalIncoming,
       fi.Moving,
        fi.Address
    FROM incoming fi
    INNER JOIN (SELECT MAX(tanggalincoming) as maxtglincoming,text18,moving
                 FROM incoming
                 GROUP BY text18) ri
     ON ri.maxtglincoming = fi.tanggalincoming AND
            ri.text18=fi.text18
    INNER JOIN md_login AS mdl ON (ri.text18=mdl.log_deviceimei AND
    mdl.log_id='alex')
    INNER JOIN master_device AS md ON md.device_imei=mdl.log_deviceimei
    GROUP BY fi.text18
    ORDER BY md.Device_PoliceNumber ASC

A little explanation about the query: So I was using MAX(tanggalincoming) at first to get the row result based on the latest update from table call incoming. the next step is: I was doing the inner join from the latest incoming table with the full incoming table so the data that will return is based from the latest incoming data that already inner joined.

And here is the sample data result that will be shown when I execute the query in mysql. There can be result more than 1 row data since 1 username can have more than 1 vehicle.

Imei - 012896001194123
Device_PoliceNumber - B 7777 GHI
Lat - -6.27585
Lng - 106.66172
Speed 0
Gps_Signal F
Battery - F:4.18V
Charging - 1
Oil - Null
Temperature - Null
Picture - livina_grey.png
EngineStatus - OFF
TanggalIncoming - 2017-05-31 05:25:59
Moving - STOP 
Address - Example Street 

But when I try to execute the query on sql server 2000, there's the error showing like this:

Server: Msg 8120, Level 16, State 1, Line 1.
Column 'md.Device_PoliceNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So the main question is: How can I achieve the same result in the sql query?

Answer 1

If you use aggregate functions (like MAX, SUM etc) in Sql Server, you should include all other fields in GROUP BY clause.

In this case in your sub-query you have SELECT MAX(tanggalincoming) as maxtglincoming,text18,moving but only text18 is included into GROUP BY. Should look like this:

SELECT MAX(tanggalincoming) as maxtglincoming,text18,moving
FROM incoming
GROUP BY text18,moving

The second is you don't have any aggregate functions in the big query. So you should remove it.

If you used GROUP BY to suppress duplications, use DISTINCT instead