2

I'm dealing with sql and my sql is not as good as my ruby, so I hope you can help me out.

I have da table that looks like this:

messages:
id INTEGER PRIMARY KEY AUTO_INCREMENT
to VARCHAR(255)
text text
shown DATETIME

For now my script generates this part depending on the number of online players.

"to = 'STEAM_0:0:xxx' OR to = 'STEAM_0:0:xxy' OR to = 'STEAM_0:0:xxz'"

It's a listening of active players and I want to check weather they have some unread messages. Now with that string I can do and a sprintf with this :

SELECT * FROM messages WHERE shown IS NULL AND (%s)"

and get a nice formated string:

SELECT * FROM messages WHERE shown IS NULL AND (to = 'STEAM_0:0:xxx' OR to = 'STEAM_0:0:xxy' OR to = 'STEAM_0:0:xxz')

NOW I have just two problems:

  1. The sql returns more then 1 entry for every field entry to, I would like to return exactly one message for every to (LIMIT 1 by to?) and it has to be the newest (first by id).

To make this clearer, let's assume I have a table like this:

id, to,  text
1,  "x", "text1"
2,  "x", "text2"
3,  "y", "text3"
4,  "z", "text4"
5,  "y", "text5"
6,  "z", "text6"
7,  "y", "text7"

I want to get the following:

1,  "x", "text1"
3,  "y", "text3"
4,  "z", "text4"
  1. I would like to update the field shown within the same SQL call to NOW() for the retrieved entries.
Improve this question
3
  • where uniqueid is in the table ? Commented Oct 31, 2010 at 5:21
  • 'to' is a really bad name for a column. It is a reserved word in MYSQL so you will need to escape or identify it as such with the table prefix each time you refer to it. It's likely you or someone else will occasionally forget to do this and then it will annoy you. Maybe something like recipient would be a better choice? See here for more details: dev.mysql.com/doc/refman/5.1/en/reserved-words.html Commented Oct 31, 2010 at 7:09
  • escape it with to, I don't care, I want a real solution and not some nagging about conventions. Commented Oct 31, 2010 at 7:26

2 Answers 2

Reset to default
1

Edit -

You can use this query - 

Select T1.[id],T1.[to],T1.[text] from Messages T1
inner join
   ( Select [to], min([id]) as id from Messages group by [to] ) T2
on T1.[id] = T2.id

Am assuming newest by id as minimum value of id while grouped by [to]. Otherwise, you can use max([id])

This would yield - 

1,  "x", "text1"
3,  "y", "text3"
4,  "z", "text4"

And, you can use the same query to get the ids for which [shown] column has to updated with current datetime - 

UPDATE messages SET shown = Now() 
where [id] in 
( 
   Select T1.[id] from messages T1 
   inner join 
   ( Select [to], min([id]) as id from messages group by [to]) T2
   on T1.[id] = T2.id
)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Oh ok.. will edit my answer shortly according to your updated question.
Awesome, I really should read these simple sql techniques up. Never faced something like this before. Thanks mate, once I get my vote up rights, I'll vote it even up.
Damn it, I noticed that i want only ([to] = "x" and [to] = "y"), can you incorporate that as a subsequent code block show off? I'm really not that good with sql ...
You can simply add a where clause to the same query. Try adding - WHERE [to] IN ('x','y')
The second sql gives me headaches, is test1 = messages?
|
0

Regarding point #1 You have no field "uniqueid" in your table. I assume you mean "id" (your primary key)? If this is the case you will need to either update the where clause (changes uniqueid to id) or simply do nothing other than edit your post so we know that there is an additional field.

Let's assume that you have one field called 'id' and that uniqueid was a typo (also replace %s with your string)

select count(*), id 
from messages 
where shown is null and (%s) 
group by id

will give you the count of unread messages for each id

Regarding point #2: You can't run an update and a Select in the same query. You will need to make two separate SQL calls.

update messages 
set shown = Now() 
where [some where clause here]
Improve this answer

1 Comment

I have reedited the post and your assumption was not correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.