| Author |
 Topic  |
|
|
cognito
Starting Member
4 Posts |
 Posted - 04/27/2012 : 03:05:21
|
Select top 1 End_time from logs.dbo.logs_tbl Inner Join character.dbo.message_tbl on logs.dbo.logs_tbl.id=character.dbo.message_tbl.id
Where character.dbo.message_tbl.id='".$string."' order by End_time desc
My query is meant to select the max or largest value from column end_time, it should return the top 1 End_time or max End_time for each row in the $string in the where clause, help me figure this out, thanks.
|
Edited by - cognito on 04/27/2012 03:13:01
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 04/27/2012 : 03:32:08
|
quote:
Originally posted by cognito
Select top 1 End_time from logs.dbo.logs_tbl Inner Join character.dbo.message_tbl on logs.dbo.logs_tbl.id=character.dbo.message_tbl.id
Where character.dbo.message_tbl.id='".$string."' order by End_time desc
My query is meant to select the max or largest value from column end_time, it should return the top 1 End_time or max End_time for each row in the $string in the where clause, help me figure this out, thanks.
Please post some sample data and an example value for the String in the Where Clause.
What do you mean when you say "for each row in the $string in the where clause"?
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
 |
|
|
Lumbago
Norsk Yak Master
Norway
3245 Posts |
Posted - 04/27/2012 : 04:54:14
|
If your .$string variable has more than one value you need to use IN instead of =.
Select character.dbo.message_tbl.id, max(End_time) as MaxEndTime
from logs.dbo.logs_tbl
Inner Join character.dbo.message_tbl
on logs.dbo.logs_tbl.id=character.dbo.message_tbl.id
Where character.dbo.message_tbl.id IN (".$string.")
group by character.dbo.message_tbl.id
order by End_time desc
- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
cognito
Starting Member
4 Posts |
Posted - 04/27/2012 : 06:23:08
|
The string is another php function which returns a list of ids in a table,
function id()
$query = mssql_query("Select name,id from character.dbo.character_tbl Where name='".$character."'");
while($row = mssql_fetch_array($query))
{
return $row[id];
}
In the next function is a list of ids that are on the contact list of the ids in the other function. The max(end_time) or top 1 works only for the first contact, I need the last End_time for each contact. I'm not sure. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 04/27/2012 : 15:19:36
|
quote:
Originally posted by cognito
Select top 1 End_time from logs.dbo.logs_tbl Inner Join character.dbo.message_tbl on logs.dbo.logs_tbl.id=character.dbo.message_tbl.id
Where character.dbo.message_tbl.id='".$string."' order by End_time desc
My query is meant to select the max or largest value from column end_time, it should return the top 1 End_time or max End_time for each row in the $string in the where clause, help me figure this out, thanks.
learn to use shortaliases rather than repeating four part name everywhere
Select top 1 End_time from logs.dbo.logs_tbl l
Inner Join character.dbo.message_tbl m
on l.id=m.id
Where m.id in IN (".$string.")
group by m.id
order by End_time desc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cognito
Starting Member
4 Posts |
Posted - 04/27/2012 : 18:05:40
|
thanks, that saves time. trying to do this query now,
Update character.dbo.character_tbl Set last_access=MAX(logs.dbo.logs_tbl.End_Time)
from logs.dbo.logs_tbl Inner Join character.dbo.character_tbl
On logs.dbo.logs_tbl.Id=character.dbo.character_tbl.Id
Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 04/28/2012 : 19:04:42
|
it should be
Update c
Set c.last_access= l.Max_End_Time
from (SELECT Id,MAX(End_Time) AS Max_End_Time
FROM logs.dbo.logs_tbl
GROUP BY Id) l
Inner Join character.dbo.character_tbl c
On l.Id = c.Id
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
max or top 1
