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
--