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?
>
Showing posts with label message. Show all posts
Showing posts with label message. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Query Timeout
I have a query I run in MSDE that will give me a timeout error message. It
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.
Monday, February 20, 2012
query syntax issue
This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What is the difference between
use [aes50] DBCC SHRINKFILE = (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', = TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
UNICODE
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?
--=_NextPart_000_000B_01C621B6.39640AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
From BOL
Unicode strings
Unicode strings have a format similar to character strings but are =preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, ='Mich=E9l' is a character constant while N'Mich=E9l' is a Unicode =constant. Unicode constants are interpreted as Unicode data, and are not =evaluated using a code page. Unicode constants do have a collation, =which primarily controls comparisons and case sensitivity. Unicode =constants are assigned the default collation of the current database, =unless the COLLATE clause is used to specify a collation. Unicode data =is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode =Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
From BOL
Unicode strings
Unicode strings have a format similar to character =strings but are preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, 'Mich=E9l' is a =character constant while N'Mich=E9l' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. =Unicode constants do have a collation, which primarily controls comparisons and =case sensitivity. Unicode constants are assigned the default collation of the =current database, unless the COLLATE clause is used to specify a collation. =Unicode data is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced =collations.
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0--
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What is the difference between
use [aes50] DBCC SHRINKFILE = (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', = TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
UNICODE
"Mark"
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?
--=_NextPart_000_000B_01C621B6.39640AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
From BOL
Unicode strings
Unicode strings have a format similar to character strings but are =preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, ='Mich=E9l' is a character constant while N'Mich=E9l' is a Unicode =constant. Unicode constants are interpreted as Unicode data, and are not =evaluated using a code page. Unicode constants do have a collation, =which primarily controls comparisons and case sensitivity. Unicode =constants are assigned the default collation of the current database, =unless the COLLATE clause is used to specify a collation. Unicode data =is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode =Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
From BOL
Unicode strings
Unicode strings have a format similar to character =strings but are preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, 'Mich=E9l' is a =character constant while N'Mich=E9l' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. =Unicode constants do have a collation, which primarily controls comparisons and =case sensitivity. Unicode constants are assigned the default collation of the =current database, unless the COLLATE clause is used to specify a collation. =Unicode data is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced =collations.
"Mark"
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0--
Subscribe to:
Posts (Atom)