SQL: distinct in where clause and fetch the result

Question

I am trying this from last 2 hours and used many methods like MIN(), DISTINCT and CONCAT() but couldn't able to get my desired result. Need an expert to take a look at my code and tell me where i am doing wrong.

My Database Structure is


ID  | message_security_code | employer_ID | seeker_ID | chat_message  | sent_by  | dated
---------------------------------------------------------------------------------------------
1   | 66666666              |  45         |  78       |  hello        | employer | 2017-05-23
2   | 44444444              |  45         |  78       |  hello        | seeker   | 2017-05-23
3   | 55555555              |  45         |  78       |  hello        | employer | 2017-05-23
4   | 66666666              |  45         |  78       |  hello        | employer | 2017-05-23

I want to fetch all results but only one record will show if the message_security_code the latest one order by ID also where employer_ID = my given id

this is what i am trying from few last hours

TRY 1

"SELECT DISTINCT message_security_code FROM pp_chat_messages WHERE employer_ID = '$id'"

TRY 2

"SELECT MIN(ID) AS ID, employer_ID, seeker_ID, chat_message, sent_by, dated FROM pp_chat_messages WHERE employer_ID = '$id' GROUP BY message_security_code"

Expected Result : if the employer_ID is 45

ID  | message_security_code | employer_ID | seeker_ID | chat_message
----------------------------------------------------------------------
1   | 66666666              |  45         |  78       |  hello
2   | 44444444              |  45         |  78       |  hello
3   | 55555555              |  45         |  78       |  hello

`A Database Error Occurred Error Number: 1096 No tables used SELECT * Filename: /home/choudharyjji/public_html/caliphp/models/chat_message_model.php Line Number: 113` — Rohit Poonia, May 23 '17 at 08:18
See https://stackoverflow.com/questions/6927940/mysql-error-1096-no-tables-used-codeigniter for information about that error in CodeIgniter — Barmar, May 23 '17 at 08:20
they are the next two columns which i didn't added in my question structure — Rohit Poonia, May 23 '17 at 08:21
This is an earliest-per-group problem and has been answered numerous times here in SO. You can easily find a solution to a problem that is similar to your if you just google sth like *first row in each group MySQL* — Giorgos Betsos, May 23 '17 at 08:24
Sorry @GiorgosBetsos i didn't mean to duplicate things here . did google many times when i didn't get anything i reached here. Sorry again for any inconvenience — Rohit Poonia, May 23 '17 at 08:27
Just replace `max()` in the duplicate topic with `min()` and there you go. There are questions specifically for `min()` as well, but the explanation is a lot better in the linked `max()` question. — Shadow, May 23 '17 at 08:28
@Shadow tried it still gets nothing please check once where i am doing wrong `"SELECT ID, MIN(message_security_code) FROM pp_chat_messages GROUP BY ID"` — Rohit Poonia, May 23 '17 at 08:37

SQL: distinct in where clause and fetch the result

0 Answers0