MDAC Installation Failure

When trying to install MDAC 2.7 on a Windows 98 client I
receive the following message:
Setup has determined that you do not have enough free
disk space to install this software. The following box
shows how much disk space is required on each drive.
....and there is nothing displayed in the box. If I
click the next button the setup exits. There are 5GB free
disk space available. Thanks in advance for any
suggestions.Look for a file called dasetup.log. This log is the MDAC setuyp log. This
log may have more details. The event logs may also have more details.
Make sure the system drive has enough space.
Thanks
Deepali
This posting is provided "AS IS" with no warranties, and confers no rights

MDAC and SQL Server 2005

In the SQL Server 2005 upgrade advisor help file it states the following...

"Upgrade Advisor detected an older version of MDAC. MDAC versions before MDAC 2.8 SP1 do not support named instances. To allow application connections to named instances, upgrade to MDAC 2.8 SP1."

I've installed our application which uses ADO (not ADO.NET) to connect to an SQL Server named instance on a client machine. Our application installs MDAC 2.6. Therefore on older machines such as Windows 95 MDAC is upgraded to 2.6. Our application can still connect to the named instance without MDAC 2.8 SP1 being present.

Is this statement in the upgrade advisor entirely true or am I misunderstanding what it means?

Thanks.MDAC 2.6 does support named instances. However, SQL Server 2005 requires MDAC 2.8SP1 or higher

Hope this helps.

Regards
Uwa|||Thanks for the reply. When you say SQL Server 2005 requires MDAC 2.8SP1 or higher, do you mean being able to use SQL Server itself on the machine it is installed on or do you mean 2.8SP1 is required by clients for connectivity.

I've got a client machine here with MDAC 2.6SP2 installed where our application can perfectly connect to my server which is running SQL Server 2005 in a named instance.

Thanks|||SQL Server 2005 requires MDAC 28SP1 or higher on the machine it's installed on. Client machines with any MDAC version can connect to it but with some possible limitations (for example there is no support for named instances prior to MDAC 26).

For better performance and if you use the new data types in SQL Server 2005 or other cool new features (MARS, notifications etc), I would recommend you use the new SQLNCLI (SQL Server native client) which has the equivalent of the OLEDB provider and ODBC Driver in one DLL package. It has it's own MSI installer that is on the Server media. Please see http://support.microsoft.com/?kbid=910016 for more info|||Thanks for the information. My main concern is getting our existing applications that are based on SQL Server 2000 working with SQL Server 2005 so I am just concerned about MDAC at the moment.

I will certainly check out SQLNCLI though as that sounds interesting.

Thanks|||SQL Native Client is a good choice if you are using new features in SQL Server 2005. It's peformance is likely to be better than MDAC in many cases and it also scales better on multiprocessor machines.

However, if you have deployed clients and you are simply upgrading the server to SQL Server 2005 there is no requirement to upgrade from MDAC to SQL Native Client.|||

Thanks guys for good info.

I have a similar question. We have an application (C++ and built in 32bit) using ODBC driver to connec to SQL Server 2000 or Oracle 9 server. Can our application connect to SQL Server 2005 (64bit) without modification? We dont mind to install MDAC 2.8SP1 on the SQL Server 2005 (64bit) machine.

I would appreciate your help.

John

|||

ODBC connectivity to SQL Server 2005 on 64bit is supported either via MDAC or the new SQL Native Client ODBC driver (which offers better performance and features in some areas).

And by the way, MDAC 28 is part of the Windows 2003 and XP operating systems so you will not need to install this.

Hope this helps.

Uwa

MDAC and SQL Server 2005

In the SQL Server 2005 upgrade advisor help file it states the following...

"Upgrade Advisor detected an older version of MDAC. MDAC versions before MDAC 2.8 SP1 do not support named instances. To allow application connections to named instances, upgrade to MDAC 2.8 SP1."

I've installed our application which uses ADO (not ADO.NET) to connect to an SQL Server named instance on a client machine. Our application installs MDAC 2.6. Therefore on older machines such as Windows 95 MDAC is upgraded to 2.6. Our application can still connect to the named instance without MDAC 2.8 SP1 being present.

Is this statement in the upgrade advisor entirely true or am I misunderstanding what it means?

Thanks.MDAC 2.6 does support named instances. However, SQL Server 2005 requires MDAC 2.8SP1 or higher

Hope this helps.

Regards
Uwa|||Thanks for the reply. When you say SQL Server 2005 requires MDAC 2.8SP1 or higher, do you mean being able to use SQL Server itself on the machine it is installed on or do you mean 2.8SP1 is required by clients for connectivity.

I've got a client machine here with MDAC 2.6SP2 installed where our application can perfectly connect to my server which is running SQL Server 2005 in a named instance.

Thanks|||SQL Server 2005 requires MDAC 28SP1 or higher on the machine it's installed on. Client machines with any MDAC version can connect to it but with some possible limitations (for example there is no support for named instances prior to MDAC 26).

For better performance and if you use the new data types in SQL Server 2005 or other cool new features (MARS, notifications etc), I would recommend you use the new SQLNCLI (SQL Server native client) which has the equivalent of the OLEDB provider and ODBC Driver in one DLL package. It has it's own MSI installer that is on the Server media. Please see http://support.microsoft.com/?kbid=910016 for more info|||Thanks for the information. My main concern is getting our existing applications that are based on SQL Server 2000 working with SQL Server 2005 so I am just concerned about MDAC at the moment.

I will certainly check out SQLNCLI though as that sounds interesting.

Thanks|||SQL Native Client is a good choice if you are using new features in SQL Server 2005. It's peformance is likely to be better than MDAC in many cases and it also scales better on multiprocessor machines.

However, if you have deployed clients and you are simply upgrading the server to SQL Server 2005 there is no requirement to upgrade from MDAC to SQL Native Client.|||

Thanks guys for good info.

I have a similar question. We have an application (C++ and built in 32bit) using ODBC driver to connec to SQL Server 2000 or Oracle 9 server. Can our application connect to SQL Server 2005 (64bit) without modification? We dont mind to install MDAC 2.8SP1 on the SQL Server 2005 (64bit) machine.

I would appreciate your help.

John

|||

ODBC connectivity to SQL Server 2005 on 64bit is supported either via MDAC or the new SQL Native Client ODBC driver (which offers better performance and features in some areas).

And by the way, MDAC 28 is part of the Windows 2003 and XP operating systems so you will not need to install this.

Hope this helps.

Uwa

maximum row size exceeds

hi,
I receive following Warning can any one tell me why this is occurs and how
can I eliminate it.
Warning: The table 'Part_Tags' has been created but its maximum row size
(14647) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
Farhan Iqbal
On Mon, 24 May 2004 02:52:29 -0700, Farhan Iqbal wrote:

>hi,
>I receive following Warning can any one tell me why this is occurs and how
>can I eliminate it.
>
>Warning: The table 'Part_Tags' has been created but its maximum row size
>(14647) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
>of a row in this table will fail if the resulting row length exceeds 8060
>bytes.
>
>Farhan Iqbal
>
Hi Farhan,
Your table definition includes at least one, probably more varchar,
nvarchar of varbinary columns. The theoretic maximum number of bytes in a
row, if all these columns are filled with the maximum length for that
column, is 14,647 bytes. The minimum length (is all varying length columns
are length zero) is below 8,060 bytes.
This is a warning message, there is no error -yet! But as soon as you try
to insert values into the table that make the total length of one row
exceed 8,060 bytes, you will get an error. SQL Server can't handle rows
that exceed 8,060 bytes of data.
Simple repro script to try it for yourself:
-- this will yield a warning similar to the one above,
-- but the table will be created.
create table testit(pk int not null primary key,
vc1 varchar(6000) not null,
vc2 varchar(6000) not null)
go
-- total length < 8,060 - no error
insert testit (pk, vc1, vc2)
select 1, replicate ('x', 3000), replicate ('y', 3000)
go
-- total length > 8,060 - error and insert rejeected
insert testit (pk, vc1, vc2)
select 2, replicate ('x', 6000), replicate ('y', 6000)
go
-- check that only first row was inserted
select * from testit
go
-- growing data beyond 8,060 bytes fails as well
update testit
set vc1 = replicate ('z', 6000)
where pk = 1
go
-- check that row was not updated
select * from testit
go
-- cleanup
drop table testit
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Simply change design of your table to mark column as text

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>|||cheers

maximum number of tables in a query (260) was exceeded

I am getting the following error message from a view I have created. I
would think it was a design issue but it appeared to be working at one time.
When it was reopened I received the following error:
--Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (260) was exceeded.
Any ideas on how to correct this? Thanks.
DonHi
If there are 260 tables in the query then you will need to think about the
design. You may have subsequently changed a view to include more tables it
was working previously.
John
<dbj> wrote in message news:uj39oq$UFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I am getting the following error message from a view I have created. I
>would think it was a design issue but it appeared to be working at one
>time. When it was reopened I received the following error:
> --Could not allocate ancillary table for view or function resolution.
> The maximum number of tables in a query (260) was exceeded.
> Any ideas on how to correct this? Thanks.
> Don
>

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum Memory Configurations

What is the maximum amount of memory SQL2K can address per instance using the following configuration
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.
It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.

Maximum Memory Configurations

What is the maximum amount of memory SQL2K can address per instance using the following configuratio
WIN 2003 32 bit Enterprise Edition OS with 32 GB RA
SQL2K SP3 32 bit Enterprise Edition w/ hot fixe
AWE Enable
/PAE switch in boot.in
Lock pages in memory enabled for MSSQL Service Account
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =addressable RAM. In your scenario you could configure three instances =to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =would allow 4GB of free memory for the OS to work with. Of course you =could install more instances, but you would want to limit the amount of =RAM each uses so that you do not run into problems by trying to allocate =more memory than you have in the server.
-- Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =using the following configuration
> > WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
> > The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =Advanced Server but has no mention of max memory allowed.

Maximum Memory Configurations

What is the maximum amount of memory SQL2K can address per instance using th
e following configuration
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Se
rver but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.

Maximum allowed value for integer in Analysis Services

Is there a maximum number up to which Analysis Services will do a SUM up to.

The reason I ask is that I get a negative number when I run the following MDX ([Mesaure] is aggregated with SUM)

SELECT [Measures].[Measure] ON AXIS(0)
FROM [Cube]

However each row in my fact table has a positive (and quite large value) for its Measure.

Thanks in advance

Stephen

Yes. There a maximun value of integer, I believe its just over 2 billion or so. When you first built your cube, AS defaulted your measure to the datatype in your source. You will have to change the datatype to a bigint, then you won't see those negative values.

Van Dieu

|||Sweet. Bigint it is. Don't know why it never occured to me to change the datatype of the Measure in Analysis Services.

Oh well, the simple things.

maximum allowed size for any data type

Hi,
I am a bit of a novice so please excuse any blinding ignorance
I recieve the following error using mssqlserver v7_1:
the size (32672) given to the type 'varchar' exceeds the maximum allowed for any data type
so how can I find out what this maximum is?search books online for data types

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

MAXFILE Size won't Change

SQL Server 2005 64 Bit Enterprise Edition.

When I run the the following command I get command completed successfully.

ALTER DATABASE Apollo_Replication
MODIFY FILE (NAME = Apollo_Replication_log,
MAXSIZE = 'UNLIMITED')

However

when I look at the database properties the file growth is still

restricted to 2 GB. I even tried to edit the property through

SSMS. Same results...It looks like it saves ok but when I go back

in... no change.
The only thing unique about the db is it is the subscriber from an Oracle publisher.
Has anyone seen this little feature before?Are you sure it is 2GB and not 2TB? The architectural limit for each log file is 2TB.|||oops it is 2 TB. But why can't I change the properties to unlimited file growth? My current size is 31 GB. I am trying to shrink the file back down to a reasonable size and change the properties.|||

UNLIMITED is never really unlimited. For log files it is 2TB. For data files it is actually 16TB. Its just a reporting issue on what the UI or report considers to be "unlimited."

Max() and Query...

Hi i got the following problem... i got a Table with multiple Scores for
People.... Something Like This
ID NAME POINTS
1 JOE 15
2 JOE 20
3 CHRIS 19
4 MATT 40
5 CHRIS 1
6 MATT 30
i need a query that will return the max of points that a player did along
with the name of the player and the id, if i didn't have the id i just
could group by, but the id is messing my group by, and returning me
everything... how can i do it '
Thanks.
AlexAlejandro K. wrote:
> Hi i got the following problem... i got a Table with multiple Scores
> for People.... Something Like This
> ID NAME POINTS
> 1 JOE 15
> 2 JOE 20
> 3 CHRIS 19
> 4 MATT 40
> 5 CHRIS 1
> 6 MATT 30
> i need a query that will return the max of points that a player did
> along with the name of the player and the id, if i didn't have the id
> i just could group by, but the id is messing my group by, and
> returning me everything... how can i do it '
> Thanks.
> Alex
Select ID,Name,maxpoints
FROM table t inner join (
SELECT Name,Max(Points) maxpoints
FROM table
GROUP BY Name) q
on t.Name=q.Name and t.points = q.maxpoints
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Try using a derived table:
create table t1 ([ID] int identity, [NAME] varchar(16), POINTS int)
insert into t1 ([NAME], POINTS) values ('JOE', 15)
insert into t1 ([NAME], POINTS) values ('JOE', 20)
insert into t1 ([NAME], POINTS) values ('CHRIS', 19)
insert into t1 ([NAME], POINTS) values ('MATT', 40)
insert into t1 ([NAME], POINTS) values ('CHRIS', 1)
insert into t1 ([NAME], POINTS) values ('MATT', 30)
select a.[ID],
b.[NAME],
b.[POINTS]
from t1 a,
(select [NAME],
MAX(POINTS) as POINTS
from t1
group by [NAME]) b
where a.[NAME] = b.[NAME]
and a.POINTS = b.POINTS
-- Jesse
"Alejandro K." <AleK@.AleK.com> wrote in message
news:Xns95F8A6D6A6BD2AleKAleKcom@.207.46.248.16...
> Hi i got the following problem... i got a Table with multiple Scores for
> People.... Something Like This
> ID NAME POINTS
> 1 JOE 15
> 2 JOE 20
> 3 CHRIS 19
> 4 MATT 40
> 5 CHRIS 1
> 6 MATT 30
> i need a query that will return the max of points that a player did along
> with the name of the player and the id, if i didn't have the id i just
> could group by, but the id is messing my group by, and returning me
> everything... how can i do it '
> Thanks.
> Alex