CODEWITHSUNDEEP
phpmysqlsql
Sates
Sates

Reputation: 408

Retrieving data from SQL table

I have a table in which all action logs are stored. It contains "user_id" (to mark user), "statement_id" (to mark statement that has been worked on), "action_date" (to mark action time) and "action_type" (to mark what action was done). A user can work on many statements on which many actions can be performed.

I need to produce a query that will show what statements were worked on at a specific time (let's say from March 1, 2018 to March 30, 2018) - that I can do. The difficulty for me is to limit the outcome. I mean precisely how to limit the outcome from this:

Statements that were worked on: 30, 30, 30, 18, 18, 42, 42, 42

To this:

Statements that were worked on: 30, 18, 42

I tried to work with queries like this one but I'm no professional. I find it difficult to understand the concept of some limiting SQL commands.

SELECT * FROM action_log 
WHERE user_id = 1 
AND action_date >= 1519858800 
AND action_date <= 1522447200 
GROUP BY user_id 
HAVING user_id = 1 
ORDER BY id DESC

I would be very grateful for any help.

Upvotes: 1

Views: 65

Answers (3)

Roshni hegde
Roshni hegde

Reputation: 415

You can use between for comparing action_date. And you can eliminate having condition because that is already used in where. WHERE user_id = 1 and HAVING user_id = 1 will result you the same. Hope following code will work for you.

SELECT * FROM action_log WHERE user_id = 1 AND action_date between '1519858800' AND '1522447200' GROUP BY statement_id ORDER BY id DESC

Upvotes: 0

Dani
Dani

Reputation: 51

I guess you only need unique values. In SQL there is a special statement for this. Use SELECT DISTINCT

SELECT DISTINCT * FROM action_log WHERE user_id = 1 AND action_date >= 1519858800 AND action_date <= 1522447200 GROUP BY user_id HAVING user_id = 1 ORDER BY id DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you are looking for SELECT DISTINCT:

SELECT DISTINCT statement_id
FROM action_log al
WHERE user_id = 1 AND
      action_date >= 1519858800 AND action_date <= 1522447200
ORDER BY statement_id DESC;

You might find this easier to maintain by using UNIX_TIMESTAMP():

SELECT DISTINCT statement_id
FROM action_log al
WHERE user_id = 1 AND
      action_date >= UNIX_TIMESTAMP('2018-03-01') AND
      action_date <= UNIX_TIMESTAMP('2018-03-30')
ORDER BY statement_id DESC;

Upvotes: 3

Related Questions