How to get distinct rows with all columns between two dates in sql?

Question

I'm trying to get all records with uniq serial numbers between two dates. But I couldn't write correct sql.

Here is my current sql query and results. There are 3 records with two XYZABC1230 serial number.

select * from t_recorded_test
where  office_id = '1710011001123'
and    record_date > '2017-11-01'
and    record_date < '2017-12-08'
and    test_result = 'true';

MODEM_MODEL MODEM_SERIAL_NUMBER OFFICE_ID       RECORD_DATE             RECORD_ID                           TEST_RESULT
Type2ModelB XYZABC1230          1710011001123   2017-11-01 19:35:54.0   ccf57f20d585424abc9bce781ada9dcc    TRUE
Type2ModelB XYZABC1230          1710011001123   2017-11-01 19:33:54.0   168ce13ed9644f128f7769432ad6ba2f    TRUE
Type2ModelB XYZABC12312         1710011001123   2017-12-03 19:33:54.0   ab727f836c354f159703565b9eed3331    TRUE

but I expect just two records XYZABC1230 and XYZABC12312. How can I do it?

EDIT:

I need just one result for all the same serial numbers. Date not important, but I have to use it for limit.

Answer 1

--This is for MSSQL Server
    with CTE as(
    select *,(row_number() over (partition by MODEM_SERIAL_NUMBER order by MODEM_SERIAL_NUMBER)) 'ROW_N'  from T_RECORDED_TEST   
    where OFFICE_ID='1710011001123' and RECORD_DATE > '2017-11-01' and RECORD_DATE < '2017-12-08' and TEST_RESULT = 'TRUE')
    select MODEM_MODEL,MODEM_SERIAL_NUMBER,OFFICE_ID,RECORD_DATE,RECORD_ID,TEST_RESULT  from CTE where ROW_N=1