return 7 col but DISTINCT on 3 col from 2 tables

Hello !
for MS SQL 2000

SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
tableA.ColB, tableA.ColC
FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA


I want to do a DISTINCT on

tableB.ColX, tableA.ColA, tableA.ColB
to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents

thank you for helpingwhat values do you want for the other 4 columns|||the normal values

lets say I need A, B, C, D , E, F
but DISTINCT on A, B, C (you can forget the 2 tables)

if A,B,C are identics I keep anyone of them, Max(A) for exemple

thank you|||Hi anselme

Could you post some example results
a) Of the data as it would appear BEFORE the distinct is applied OR the raw data as it would appear in the tables
b) the results you are hoping for

As an example (without distinct) a)

A B C D E F
----------
aa bb cc 1 2 3
aa bb cc 4 5 6
with distinct I think you would want b)

A B C D E F
----------
aa bb cc ?? ?? ??Is this accurate for your problem?
What would go where the ?? are?|||select A, B, C, min(D), max(E), avg(F)
from daTable
group by A, B, C|||a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.|||Yeah Rudy - come on!
You could have written a CLR aggregate function or something to better meet the business requirements stated. SQL Consultant my Aunt Fanny.|||a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.oh, boy, this is getting interesting

upalsen, if you wouldn't mind, could you please show how A,B,C,D,E,F can be redesigned so that the requirement for the query in post #4 "should not at all come up"

i'd really like to see how you do it|||sorry Rudy, you are getting me wrong. I never said that the database can be designed to address the query just how Anselme wanted. what I wanted to say is this query should not be a requirement at all as Anselme never asked for a sum or avg. rather just wanted a distinct row set from a one to many relationship that too with an outer join which appears to be nonrealastic and absurd. or the data is such that they can be there in the first table itself, if normalized properly :confused:.|||would you kindly repeat what you just said? i'm having a little trouble understanding it

also, i really want to know why you think his outer join is unrealistic and absurd|||ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions. mind that the original post never ever asked for any sum, avg, or max. it just wanted a distinct set of rows of 3 cols while selecting 7 cols from a one-to-many relationship|||there is a flaw in the original specification, which you seem to have propagated -- "a distinct set of rows of 3 cols while selecting 7 cols"

first of all, you were pretty close when you said "a distinct set of rows" because it is rows that are distinct

so if you put 7 columns into the row, you cannot do a "distinct" on just 3 of them

but i suspect you knew that already, right?

the alternative is to either

1. use DISTINCT and only have 3 columns in the SELECT

or

2. use GROUP BY on the 3 columns, but then you are forced to use an aggregate function on the other 4

my question to you -- given columns A,B,C,D,E,F, how would you produce a result set where A,B,C, are unique? what would you do for the other 4 columns?|||ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions.
OK. Just to stir the pot:select A,
B,
C,
(select top 1 D from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as D,
(select top 1 E from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as E,
(select top 1 F from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as F
from daTable
group by A,
B,
C
There. Now you can both be pi$$ed at me instead of eachother. ;)|||thank you r937, for my needs your answers works fine

min,min,min or max,max,max no matter, sometimes you need tree different girls and you dont care of the color of the shoes

thank you to everybody, and blindman for the other solution|||sometimes you need tree different girls and you dont care of the color of the shoes
...and thank you for that colorful metaphore.sql