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 charset. Show all posts
Showing posts with label charset. Show all posts
Wednesday, March 21, 2012
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)