Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continue
to be live/available. The mgr believes "there should be a way using named SQL
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only have
> access to its own SQL disks/data through SSP but this could be turned off if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would continue
> to be live/available. The mgr believes "there should be a way using named SQL
> instances and DNS aliases (or local hosts files on the servers) for this to
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
> > I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> > 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> > data and logs are on RAID arrays in the storage array. The servers only have
> > access to its own SQL disks/data through SSP but this could be turned off if
> > necessary.
> > My mgr wants me to come up with adisaster recoverysolution where - if one
> > server fails - then "the remaining SQL server can access the access the
> > databases that reside on the shared array" and those databases would continue
> > to be live/available. The mgr believes "there should be a way using named SQL
> > instances and DNS aliases (or local hosts files on the servers) for this to
> > be possible".
> > To me it seems like he is looking for cluster functionality without a
> > cluster and I can't get my mind around his proposed solution.
> > Does anyone see a way to provide the functionality the mgr is seeking?
> > Thanks
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeeperworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continue
to be live/available. The mgr believes "there should be a way using named SQL
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)

> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only have
> access to its own SQL disks/data through SSP but this could be turned off if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would continue
> to be live/available. The mgr believes "there should be a way using named SQL
> instances and DNS aliases (or local hosts files on the servers) for this to
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com
|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
>
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeeperworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continu
e
to be live/available. The mgr believes "there should be a way using named SQ
L
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)

> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP M
SA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only ha
ve
> access to its own SQL disks/data through SSP but this could be turned off
if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would conti
nue
> to be live/available. The mgr believes "there should be a way using named
SQL
> instances and DNS aliases (or local hosts files on the servers) for this t
o
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literat..._sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
>
>
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeepe
rworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literat..._sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

Disabling DISTINCT of certain selects please help!

Hi everyone,
Hope you can help me with this. I'm at my wits end. I have a table
with 5 fields in it, one of which is the key. I'm doing a:
SELECT DISTINCT column2, column3
FROM tableName
but the problem is though I need the other fields but do not want the
DISTINCT keyword to wortk on them. Do you know what I mean? In other
words:
SELECT DISTINCT column2, column3, column1, column4
FROM tableName
where column1 is the primary key and both column1 & column4 are not
effect by the distinct keyword. Can anyone out there help me please?
Any comments/suggestions/code-samples much appreciated.
Cheers,
Al.No, I don't know what you mean. Can you post some sample data and sample
output that you would like from your query?
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<almurph@.altavista.com> wrote in message
news:1183653609.489518.122550@.q75g2000hsh.googlegroups.com...
> Hi everyone,
>
> Hope you can help me with this. I'm at my wits end. I have a table
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
>
> but the problem is though I need the other fields but do not want the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
>
> where column1 is the primary key and both column1 & column4 are not
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
>|||On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi everyone,
> Hope you can help me with this. I'm at my wits end. I have a tabl
e
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
> but the problem is though I need the other fields but do not want
the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
> where column1 is the primary key and both column1 & column4 are no
t
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
You haven't explained which values you want to see for Column1 and
Column4. If you only want one row for each value of Column2 and
Column3 then there has to be some selection criterion for the other
columns. For example you might want the MIN or MAX values:
SELECT col2, col3,
MIN(col1) AS col1, MIN(col4) AS col4
FROM TableName
GROUP BY col2, col3;
Or maybe you would want the row with the first (minimum) primary key
value for each distinct Column2 and Column3:
SELECT col2, col3, col1, col4
FROM TableName AS t
WHERE col1 =
(SELECT MIN(col1)
FROM TableName
WHERE col2 = t.col2
AND col3 = t.col3);
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 5, 5:50 pm, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
> wrote:
>
>
>
>
>
>
>
>
>
> You haven't explained which values you want to see for Column1 and
> Column4. If you only want one row for each value of Column2 and
> Column3 then there has to be some selection criterion for the other
> columns. For example you might want the MIN or MAX values:
> SELECT col2, col3,
> MIN(col1) AS col1, MIN(col4) AS col4
> FROM TableName
> GROUP BY col2, col3;
> Or maybe you would want the row with the first (minimum) primary key
> value for each distinct Column2 and Column3:
> SELECT col2, col3, col1, col4
> FROM TableName AS t
> WHERE col1 =
> (SELECT MIN(col1)
> FROM TableName
> WHERE col2 = t.col2
> AND col3 = t.col3);
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -
Hi I just want to see the values them seleves nothing else.|||On 5 Jul, 17:52, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi I just want to see the values them seleves nothing else.
>
I'm sorry, but that doesn't explain anything. You are saying you want
to select only certain values for columns 1 and 4 - correct? But you
aren't explaining *which* values you want to select.
Please post enough information to reproduce the problem:
1. A CREATE TABLE statement (include the key constraints please).
2. A few INSERT statements to generate some data.
3. Show what end result you want based on that sample data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Disabling DISTINCT of certain selects please help!

Hi everyone,
Hope you can help me with this. I'm at my wits end. I have a table
with 5 fields in it, one of which is the key. I'm doing a:
SELECT DISTINCT column2, column3
FROM tableName
but the problem is though I need the other fields but do not want the
DISTINCT keyword to wortk on them. Do you know what I mean? In other
words:
SELECT DISTINCT column2, column3, column1, column4
FROM tableName
where column1 is the primary key and both column1 & column4 are not
effect by the distinct keyword. Can anyone out there help me please?
Any comments/suggestions/code-samples much appreciated.
Cheers,
Al.On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi everyone,
> Hope you can help me with this. I'm at my wits end. I have a table
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
> but the problem is though I need the other fields but do not want the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
> where column1 is the primary key and both column1 & column4 are not
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
You haven't explained which values you want to see for Column1 and
Column4. If you only want one row for each value of Column2 and
Column3 then there has to be some selection criterion for the other
columns. For example you might want the MIN or MAX values:
SELECT col2, col3,
MIN(col1) AS col1, MIN(col4) AS col4
FROM TableName
GROUP BY col2, col3;
Or maybe you would want the row with the first (minimum) primary key
value for each distinct Column2 and Column3:
SELECT col2, col3, col1, col4
FROM TableName AS t
WHERE col1 = (SELECT MIN(col1)
FROM TableName
WHERE col2 = t.col2
AND col3 = t.col3);
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 5, 5:50 pm, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
> wrote:
>
>
> > Hi everyone,
> > Hope you can help me with this. I'm at my wits end. I have a table
> > with 5 fields in it, one of which is the key. I'm doing a:
> > SELECT DISTINCT column2, column3
> > FROM tableName
> > but the problem is though I need the other fields but do not want the
> > DISTINCT keyword to wortk on them. Do you know what I mean? In other
> > words:
> > SELECT DISTINCT column2, column3, column1, column4
> > FROM tableName
> > where column1 is the primary key and both column1 & column4 are not
> > effect by the distinct keyword. Can anyone out there help me please?
> > Any comments/suggestions/code-samples much appreciated.
> > Cheers,
> > Al.
> You haven't explained which values you want to see for Column1 and
> Column4. If you only want one row for each value of Column2 and
> Column3 then there has to be some selection criterion for the other
> columns. For example you might want the MIN or MAX values:
> SELECT col2, col3,
> MIN(col1) AS col1, MIN(col4) AS col4
> FROM TableName
> GROUP BY col2, col3;
> Or maybe you would want the row with the first (minimum) primary key
> value for each distinct Column2 and Column3:
> SELECT col2, col3, col1, col4
> FROM TableName AS t
> WHERE col1 => (SELECT MIN(col1)
> FROM TableName
> WHERE col2 = t.col2
> AND col3 = t.col3);
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -
Hi I just want to see the values them seleves nothing else.|||No, I don't know what you mean. Can you post some sample data and sample
output that you would like from your query?
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<almurph@.altavista.com> wrote in message
news:1183653609.489518.122550@.q75g2000hsh.googlegroups.com...
> Hi everyone,
>
> Hope you can help me with this. I'm at my wits end. I have a table
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
>
> but the problem is though I need the other fields but do not want the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
>
> where column1 is the primary key and both column1 & column4 are not
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
>|||On 5 Jul, 17:52, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi I just want to see the values them seleves nothing else.
>
I'm sorry, but that doesn't explain anything. You are saying you want
to select only certain values for columns 1 and 4 - correct? But you
aren't explaining *which* values you want to select.
Please post enough information to reproduce the problem:
1. A CREATE TABLE statement (include the key constraints please).
2. A few INSERT statements to generate some data.
3. Show what end result you want based on that sample data.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Disabling DISTINCT of certain selects please help!

Hi everyone,
Hope you can help me with this. I'm at my wits end. I have a table
with 5 fields in it, one of which is the key. I'm doing a:
SELECT DISTINCT column2, column3
FROM tableName
but the problem is though I need the other fields but do not want the
DISTINCT keyword to wortk on them. Do you know what I mean? In other
words:
SELECT DISTINCT column2, column3, column1, column4
FROM tableName
where column1 is the primary key and both column1 & column4 are not
effect by the distinct keyword. Can anyone out there help me please?
Any comments/suggestions/code-samples much appreciated.
Cheers,
Al.
No, I don't know what you mean. Can you post some sample data and sample
output that you would like from your query?

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<almurph@.altavista.com> wrote in message
news:1183653609.489518.122550@.q75g2000hsh.googlegr oups.com...
> Hi everyone,
>
> Hope you can help me with this. I'm at my wits end. I have a table
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
>
> but the problem is though I need the other fields but do not want the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
>
> where column1 is the primary key and both column1 & column4 are not
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
>
|||On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi everyone,
> Hope you can help me with this. I'm at my wits end. I have a table
> with 5 fields in it, one of which is the key. I'm doing a:
> SELECT DISTINCT column2, column3
> FROM tableName
> but the problem is though I need the other fields but do not want the
> DISTINCT keyword to wortk on them. Do you know what I mean? In other
> words:
> SELECT DISTINCT column2, column3, column1, column4
> FROM tableName
> where column1 is the primary key and both column1 & column4 are not
> effect by the distinct keyword. Can anyone out there help me please?
> Any comments/suggestions/code-samples much appreciated.
> Cheers,
> Al.
You haven't explained which values you want to see for Column1 and
Column4. If you only want one row for each value of Column2 and
Column3 then there has to be some selection criterion for the other
columns. For example you might want the MIN or MAX values:
SELECT col2, col3,
MIN(col1) AS col1, MIN(col4) AS col4
FROM TableName
GROUP BY col2, col3;
Or maybe you would want the row with the first (minimum) primary key
value for each distinct Column2 and Column3:
SELECT col2, col3, col1, col4
FROM TableName AS t
WHERE col1 =
(SELECT MIN(col1)
FROM TableName
WHERE col2 = t.col2
AND col3 = t.col3);
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Jul 5, 5:50 pm, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> On 5 Jul, 17:40, "almu...@.altavista.com" <almu...@.altavista.com>
> wrote:
>
>
>
>
>
>
> You haven't explained which values you want to see for Column1 and
> Column4. If you only want one row for each value of Column2 and
> Column3 then there has to be some selection criterion for the other
> columns. For example you might want the MIN or MAX values:
> SELECT col2, col3,
> MIN(col1) AS col1, MIN(col4) AS col4
> FROM TableName
> GROUP BY col2, col3;
> Or maybe you would want the row with the first (minimum) primary key
> value for each distinct Column2 and Column3:
> SELECT col2, col3, col1, col4
> FROM TableName AS t
> WHERE col1 =
> (SELECT MIN(col1)
> FROM TableName
> WHERE col2 = t.col2
> AND col3 = t.col3);
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -
Hi I just want to see the values them seleves nothing else.
|||On 5 Jul, 17:52, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> Hi I just want to see the values them seleves nothing else.
>
I'm sorry, but that doesn't explain anything. You are saying you want
to select only certain values for columns 1 and 4 - correct? But you
aren't explaining *which* values you want to select.
Please post enough information to reproduce the problem:
1. A CREATE TABLE statement (include the key constraints please).
2. A few INSERT statements to generate some data.
3. Show what end result you want based on that sample data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx