How to select columns with an even ID number?

Question

I'm trying to solve the Hackerrank problem Weather Observation Station 3 which is stated below:

I came up with the following solution:

SELECT CITY
FROM STATION
WHERE MOD(ID, 2) = 0

but when I try to run it I get the compiler message "Wrong answer". I don't see what's wrong with this query?

Answer 1

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID, 2) = 0

Answer 2

You can try this query:

SELECT DISTINCT CITY FROM STATION WHERE (ID % 2) = 0 

Answer 3

For DB2:

SELECT DISTINCT CITY 
FROM STATION
WHERE MOD(ID, 2) = 0;

---

For Oracle:

SELECT DISTINCT CITY 
FROM STATION
WHERE MOD(ID, 2) = 0;

---

For MySQL:

SELECT DISTINCT CITY 
FROM STATION
WHERE MOD(ID, 2) = 0;

-- Or --

SELECT DISTINCT CITY 
FROM STATION
WHERE ID%2=0;

---

For MSSQL:

SELECT DISTINCT CITY 
FROM STATION
WHERE ID%2=0;

---

Answer 4

MYSQL:

SELECT DISTINCT CITY FROM STATION WHERE (ID % 2)=0;

ORACLE:

SELECT DISTINCT CITY FROM STATION WHERE MOD(ID, 2)=0;

Answer 5

SELECT DISTINCT CITY FROM STATION WHERE (ID % 2) = 0;

• DISTINCT for not allowing duplicates,
• (Id % 2) tells SQL to divide the Id by 2 and return the remainder.
• Since we're looking for only even values, we set this to = 0, as 2%2=0, 4%2=0, etc.
• If we were looking for odd-numbered Ids, we'd use = 1 instead, since there's always a remainder of 1 when dividing odd numbers by 2.

Answer 6

If the question would like "Query a list of CITY names from STATION with even ID numbers only" then the answer would be

select CITY from station where ID % 2 = 0;

But due the further criteria like "But must exclude duplicates from your answer." your answer should be

select DISTINCT CITY from station where ID % 2 = 0;

Answer 7

I suggest another solution:

    SELECT DISTINCT CITY
    FROM STATION
    WHERE ID LIKE '%0' OR ID LIKE '%2' OR ID LIKE '%4' OR ID LIKE '%6' OR ID LIKE '%8';

Answer 8

In MSSQL:

SELECT CITY
FROM STATION
WHERE (ID % 2) = 0
GROUP BY CITY