help with sql

i'm trying to find peak connections for each day

select max(connections)as peak from peak_connections
where account_id ='1909' and incident_time between '2004-05-25' and '2004-05-26'
-- the above query returns only the peak for 5/25.

how do I write it so that when i execute the query it will return the peak for all dates.
some sample data is provided below.

thanks for your help.

May 25, 2004 1
May 25, 2004 0
May 25, 2004 1
May 25, 2004 0
May 25, 2004 1
May 25, 2004 2
May 25, 2004 2
May 25, 2004 3
May 25, 2004 4
May 25, 2004 5
May 25, 2004 4
May 25, 2004 5
May 25, 2004 6
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 9
May 25, 2004 10
May 25, 2004 11
May 25, 2004 11
May 25, 2004 12
May 25, 2004 13
May 25, 2004 14
May 25, 2004 13
May 25, 2004 12
May 25, 2004 10
May 25, 2004 9
May 25, 2004 8
May 25, 2004 7
May 25, 2004 6
May 25, 2004 5
May 25, 2004 4
May 25, 2004 3
May 25, 2004 3
May 25, 2004 3
May 25, 2004 4
May 25, 2004 3
May 25, 2004 4
May 25, 2004 5
May 25, 2004 6
May 25, 2004 7
May 25, 2004 8
May 25, 2004 8
May 25, 2004 9
May 25, 2004 8
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 5
May 25, 2004 5
May 25, 2004 4
May 25, 2004 3
May 25, 2004 2
May 25, 2004 1
May 26, 2004 1
May 26, 2004 0
May 26, 2004 1
May 26, 2004 2
May 26, 2004 1
May 26, 2004 1
May 26, 2004 1
May 26, 2004 2
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 5
May 26, 2004 6
May 26, 2004 7
May 26, 2004 8
May 26, 2004 9
May 26, 2004 9
May 26, 2004 8
May 26, 2004 7
May 26, 2004 6
May 26, 2004 5
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 4
May 26, 2004 5
May 26, 2004 3
May 26, 2004 2
May 26, 2004 1
May 27, 2004 1
May 27, 2004 1
May 27, 2004 2
May 27, 2004 1
May 27, 2004 2
May 27, 2004 2
May 27, 2004 1
May 27, 2004 2
May 27, 2004 3
May 27, 2004 2select incident_time, max(connections)as peak from peak_connections
where account_id ='1909' and incident_time between '2004-05-25' and '2004-05-26'
group by incident_time

Books Online, anyone?

Help with MAX()

Hi,
Need a query help.
Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No
Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.
How do I do it?Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

I think that should be it ...|||Sorry. That doesn't seem to give me the result I want.

These are my data.

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372873','2005-10-10','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372874','2005-10-11','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372872','2005-10-12','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372875','2005-10-13','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372876','2005-10-14','Approved');

I just want the latest records for each Serial_No. In this case, I would want
the third and the fifth record.|||select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

SELECT *
FROM Logs INNER JOIN
(SELECT Serial_No, MAX(Log_Time) AS MaxOfLogs
FROM logs
GROUP BY Serial_No) LogMaxTime ON
Logs.Serial_No = LogMaxTime.Serial_No
AND Logs.Log_Time = LogMaxTime.MaxOfLogs :)|||It WORKS!!!
Thanks a lot!! :)|||Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

or

Select Serial_No, Log_Time , * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

I think second is the best to use. Because it is Optimised.

Regards,
Subramanyam.|||the second is not the best to use, because it is invalid syntax

:)|||...but other than that minor drawback, it is way ultra-cool.

Help with max()

I have two tables:
TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
TABLE2 has policy_id
I'm doing a join, WHERE table1.policy_id = table2.policy_id.
I need to display the value of many fields from both tables where
table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
someone point me in the right direction? Thanks.SELECT Table1.*, Table2.*
FROM Table1
JOIN Table2 ON Table1.policy_id = Table2.policy_id
WHERE Table1.Subm_No =
(
SELECT MAX(Subm_No)
FROM Table1 Tx
WHERE Tx.policy_id = Table1.policy_id
)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1d98a20592f0f5359898f9@.msnews.microsoft.com...
> I have two tables:
> TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
> TABLE2 has policy_id
> I'm doing a join, WHERE table1.policy_id = table2.policy_id.
> I need to display the value of many fields from both tables where
> table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
> someone point me in the right direction? Thanks.

Help with Max Query

Hello I have query which is pulling the winnings for each player by month and year, I total the winnings but I would only like to see the top winner for each month.

Here is the query I have, any insight would be great thanks in advance.

SELECT TOP (100) PERCENT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC

Is this what you need?:

SELECT TOP (100) PERCENT dbo.Players.Player_name, MAX(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC

|||

Hi Mariop

no I need to sum the values first so I can get the monthly total of each player, then I want to select the highest monthly total for each month.

Help with inserting multiple records using a CSV value.

I have the Temporary table:

ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))

in one instance Value might equal: "1, 2, 3, 4"

This only happens when FieldTypeID = 5.

So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])

I have created a function to split the [Value] into a table of INTs

Any Advice?

If your function returns a table type data, loop through the table and do an INSERT for each row.

|||

I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\

Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(

|||

You could do an :

(1) Declare a table variable with an additional column Processed tinyint.

(2) INSERT INTO @.table

SELECT dbo.someFunction

(3) Loop through the table.

WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)

Begin

Get the values from the @.table

Insert into the Original table

update @.table set processed = 1 Where Condition

End

|||

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

|||

RTernier:

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

Yes.

|||

That would work. Now another question (Yea, I'm not that strong in SQL :P )

While I go through the WHILE loop,

Is there a way I can grab the values of the loop I'm going through?

Example:

WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

====

I could do this right:

WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN

PRINT T.MyColumn
END

===

if not, how can I directly access the values from T?

|||

If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.

Decare @.rowid int

WHILE ...

Begin

SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0

INSERT INTO ...original table

Update @.t Set Processed = 1 Where Id = @.Rowid

End