query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakriWould the answer in this case be 650?|||it will be 650 and 700 according to user id|||Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).|||Ok - you edited your typo while I was posting :D

So - why two answers?|||ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.|||Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH|||i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.|||What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

-PatP

Query top x with sum

Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
FROM classificacao_principal
INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
Any ideas?
Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
Thanks
Ravi
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Thats it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>

Query top x with sum

Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_
principal.posicao) AS soma_pontos
FROM classificacao_principal
INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all the r
esults available. What i want is to sum only the 3 best results.
Any ideas?Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificaca
o_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the
results available. What i want is to sum only the 3 best results.
> Any ideas?|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
--
Thanks
Ravi
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificaca
o_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the
results available. What i want is to sum only the 3 best results.
> Any ideas?|||Thats it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>
>

Query top x with sum

This is a multi-part message in MIME format.
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, = SUM(classificacao_principal.posicao) AS soma_pontos
FROM classificacao_principal INNER JOIN atleta ON classificacao_principal.dorsal =3D = atleta.dorsal_atl
WHERE (atleta.sexo_atl =3D 'F' AND = classificacao_principal.id_equipa=3D'46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all = the results available. What i want is to sum only the 3 best results.
Any ideas?
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello everyone,

I'm making a query that is supposed to = sum the 3 best results.
The query is this:

SELECT top 3 = classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontosFROM classificacao_principal
INNER JOIN atleta ON = classificacao_principal.dorsal =3D atleta.dorsal_atlWHERE (atleta.sexo_atl =3D 'F' AND classificacao_principal.id_equipa=3D'46')GROUP BY classificacao_principal.id_equipaORDER BY = soma_pontos ASC

This query resturns me the top 3 = results, but the SUM is made with all the results available. What i want is to sum = only the 3 best results.
Any ideas?
--=_NextPart_000_001E_01C57BFE.5877E810--Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:
> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
--
Thanks
Ravi
"Jorge Ferreira" wrote:
> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?|||That´s it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>> Hello everyone,
>> I'm making a query that is supposed to sum the 3 best results.
>> The query is this:
>> SELECT top 3 classificacao_principal.id_equipa AS equipa,
>> SUM(classificacao_principal.posicao) AS soma_pontos
>> FROM classificacao_principal
>> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
>> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
>> GROUP BY classificacao_principal.id_equipa
>> ORDER BY soma_pontos ASC
>> This query resturns me the top 3 results, but the SUM is made with all
>> the results available. What i want is to sum only the 3 best results.
>> Any ideas?
>

query to find a sum of char values present in different rows

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

Quote:

Originally Posted by Anu139

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

I got the answer

declare @.name varchar(50)

select @.name = coalesce( @.name,'' )+ col1 from tab_name

select @.name 'Name'