i am experiencing problems with a mysql query
look, i have a table sturcture like this
| name | text | timestamp|
| Emin | Hello| 43423423 |
etc.
lets imagine it is a forum table, and name is name of author, text is topic, and timestamp is time posted,
the query needed to get one last posted record for each name in the table.
thanks
Full Version: SQL query , please help!
Something like this might work.
select max(timestamp) as xxx from yourtablename
select name, text from yourtablename where timestamp = $xxx
I you were using mysql 4.1 you could even do it as a single SQL statement using a subquery.
select max(timestamp) as xxx from yourtablename
select name, text from yourtablename where timestamp = $xxx
I you were using mysql 4.1 you could even do it as a single SQL statement using a subquery.
How about this:
The sub-select will be more efficient for databases that support it, but if you have an index created for the timestamp field the above should be efficient as well and lets you do one trip to the database instead of two (if you are using MySQL 3.x and can't do sub-selects).
CODE
SELECT
timestamp, name, text
FROM
yourtable
ORDER BY
timestamp desc
LIMIT 1;
timestamp, name, text
FROM
yourtable
ORDER BY
timestamp desc
LIMIT 1;
The sub-select will be more efficient for databases that support it, but if you have an index created for the timestamp field the above should be efficient as well and lets you do one trip to the database instead of two (if you are using MySQL 3.x and can't do sub-selects).
Dear perldok,
this query only returns one record,.
i need all the records for all the names in table
this query only returns one record,.
i need all the records for all the names in table
QUOTE
Originally posted by Emin
Dear perldok,
this query only returns one record,.
i need all the records for all the names in table
Dear perldok,
this query only returns one record,.
i need all the records for all the names in table
remove the LIMIT 1
Well, the simplest way I can think of right now (i'm tired) is:
Get the number of different users in the table with this query.
Then use the result from that in the limit clause here.
That should do the trick.
Get the number of different users in the table with this query.
QUOTE
SELECT COUNT(DISTINCT(name)) FROM table;
Then use the result from that in the limit clause here.
QUOTE
SELECT MAX(timestamp), name, text
FROM table
GROUP BY name
ORDER BY timestamp DESC
LIMIT (number from the above query)
FROM table
GROUP BY name
ORDER BY timestamp DESC
LIMIT (number from the above query)
That should do the trick.
QUOTE
Originally posted by jaume
remove the LIMIT 1
remove the LIMIT 1
Sorry, then it will give all the records not the higest
Emin,
Sorry about that, I did a total brain fart and missed the "for every name" .. my bad
.
char's SQL wihtout the LIMIT clause should do the trick.
Apologies.
Sorry about that, I did a total brain fart and missed the "for every name" .. my bad
.
char's SQL wihtout the LIMIT clause should do the trick.
Apologies.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.