CODEWITHSUNDEEP
mysqlsql
smali
smali

Reputation: 4805

selecting distinct records from a mysql table

I have table log in that I have a column Message which is storing log information. Message will contain some event IDs and description of logs so I want to read all the distinct messages with different event ID's.

What I tried 

 select distinct message from log limit 100;

I am getting all the distinct messages but with the same event ID's

Next 

select * from log where 
message not like "%1177%" and 
message not like "%609%" and 
message not like "%82%" and 
message not like "%1107%" and
message not like "%23%"
order by ID DESC limit 10;

here after getting the log I have to again add a line for getting next unique event id like if I got 23 and want to get other that 23 I have to add message not like "%23%" to get the messages other than 23 and this query will size is getting larger.

So How to write a query which will select different messages with different event ID's?

edit

Field              | Type             | Null | Key | Default | Extra                |
+--------------------+------------------+------+-----+---------+----------------+
| ID                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| FromHost           | varchar(60)      | YES  |     | NULL    |                  |
| Message            | text             | YES  |     | NULL    |                |


**edit**

sample messages

1177|Malformed DNS Packet|12-2-15
1177|Malformed DNS Packet|11-2-15
1177|Malformed DNS Packet|11-1-15
609|IDP Prevention Alert|12-2-15

609|IDP Prevention Alert|11-1-15

output

1177|Malformed DNS Packet|12-2-15
609|IDP Prevention Alert|11-1-15

Upvotes: 0

Views: 446

Answers (4)

dnoeth
dnoeth

Reputation: 60462

In Standard SQL this will be:

SELECT
   SUBSTRING(message FROM 1 FOR POSITION('|' IN message) -1) AS EventID
  ,MAX(message)
FROM tab 
GROUP BY 
   SUBSTRING(message FROM 1 FOR POSITION('|' IN message) -1)

If you don't need to show the EventId in your result you might simply remove it.

In MySQL SUBSTRING/POISITION can be replaced by SUBSTRING_INDEX (and you probably don't need the MAX, too):

SUBSTRING_INDEX(message, '|', 1)

Upvotes: 1

Rigel1121
Rigel1121

Reputation: 2016

You can achieve this using MIN() or MAX() functions since your up to different ID with unique message.

SELECT MIN(ID),message FROM log GROUP BY message

or

SELECT MAX(ID),message FROM log GROUP BY message

Upvotes: 0

Matin Rahman
Matin Rahman

Reputation: 1

SELECT DISTINCT id, message
FROM "yourTable" GROUP BY id

That should return the id along with the message distinctly.

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

 select * from log where group by message,eventID

This query will return you the logs based on unique messages along with eventIDs

Upvotes: 1

Related Questions