Help on a probably nested sql-query

Question

Hello, 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.

Answer 1

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]

Answer 2

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
)