CODEWITHSUNDEEP
phpmysql
zerey
zerey

Reputation: 871

retrieve records from a db without the same id

.what i want to do is to retrieve records from my database and display everything but the condition is not to display all records having the same task_id but to display only the newest among them. how do I select the newest among the records with the same task_id?

Upvotes: 0

Views: 121

Answers (5)

Abhay
Abhay

Reputation: 6645

This is your solution:

SELECT *
FROM 
(
    SELECT *
    FROM `your_table`
    ORDER BY `task_time` DESC
) `TT`
GROUP BY `TT`.`task_id`;

Please note to change in the above query, your_table with your table name and task_id, task_time with your respective ID and time columns.

Also, please note that the above query might run slow if the table has many, many entries, say a million rows.

Let me know if above is the query you were willing to ask.

Upvotes: 0

Lavy John
Lavy John

Reputation: 59

@rahim asgari

If you want to list only the tasks having more than one record ,then

SELECT * FROM TBL GROUP BY task_id HAVING count(task_id)>1 ORDER BY date DESC

Upvotes: 0

Nicholas Spragg
Nicholas Spragg

Reputation: 71

For MYSQL TIMESTAMP it is good practice to specify the default and on update action

last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Upvotes: 0

Yanick Rochon
Yanick Rochon

Reputation: 53546

You will need a column like

last_modified TIMESTAMP

so each time a row is inserted, or updated, the last_modified column gets updated with the current timestamp. Then, all you need is a query like

SELECT *
  FROM tblTask
 WHERE task_id = n
 ORDER BY last_modified DESC
 LIMIT 5

Will fetch the latest 5 updated tasks from the database with the given task_id. Remove the WHERE clause to get the newest tasks regardless of the task_id.

note: the TIMESTAMP data type is special, in the sense that MySQL will update the field automatically, by default, to the current timestamp, unless a value is explicitly defined in the insert or update query. If this behavior is not wanted, use DATETIME, but it will require that you specify the column value for the new column (unless a default value is set in the table)

Upvotes: 0

rahim asgari
rahim asgari

Reputation: 12437

If you have a date field (actually timestamp) in your database u can use group by and order by to do the job:

SELECT * FROM TBL GROUP BY task_id ORDER BY date DESC

Upvotes: 1

Related Questions