max or top 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

max or top 1

Author

Topic

cognito
Starting Member

4 Posts

Posted - 2012-04-27 : 03:05:21

[code]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[/code]
[code]Where character.dbo.message_tbl.id='".$string."' order by End_time desc[/code]
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.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 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

3271 Posts

Posted - 2012-04-27 : 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 - 2012-04-27 : 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

52326 Posts

Posted - 2012-04-27 : 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 - 2012-04-27 : 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

52326 Posts

Posted - 2012-04-28 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources