Help on a probably nested sql-query

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.

Answers


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
)

Need Your Help

how to increase speed of timer object using swift

ios iphone swift xcode6

I am using an "NSTimer.scheduledTimerWithTimeInterval" object but cannot increase the speed of the clock to faster than 1/1000. I have tried invalidating the clock and re-creating. timer2 & tim...

Is there any other way for this procedure?

sql-server

I have 7 fields in a table; possible combinations of fields generate some result. A combination can be 1 or 4 or 7 or all and so on, which can be changed dynamically as given by client. I have to c...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.