finding DISTINCT combinations

Hi,

I need to get 8 columns from a table.
1)
I must get only one entry of each unique combination of columsn 1,2,3.
2)
These entries must have the max. possible value in column 4.

Currently I have,

SELECT DISTINCT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO #temp
FROM TABLE1 A

INSERT INTO ANSWERTABLE
(one, two, three, four, five, six, seven, eight)

SELECT one, two, three, four, five, six, seven, eight
FROM #temp A
WHERE A.four = (SELECT MAX(B.four)
FROM TABLE B
WHERE B.one = A.two
AND B.two = A.two
AND B.three = A.three);my policy is, i don't answer what is obviously a homework problem unless you have made an attempt yourself, and in this case, it looks like you have

either i don't understand the problem, or else it's a lot simpler than you thinkselect one, two, three, max(four)
from yourtable
group by one, two, threeGROUP BY always yields distinct combinations

a good homework assignment makes you stop and think, doesn't it

"distinct" does not imply you have to use the DISTINCT keyword

rudy
http://r937.com/|||believe it or not, but this isn't a homework question. I just simplified the table and column names so that it would be easier to understand.

Thanks for your solution though.

But i need to be able to get entries from almost all 8 columns (ie. other columns which do not have to be distinct).|||Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()|||Originally posted by DoktorBlue
Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()

that's the thing, i don't want to use an aggregate function on those other columns.

The database has numerous entries for each unique combination of columns one, two and three. And I want the entire row for the entry that has the max value at column 4.

thanks for the help though.|||I want the entire row for the entry that has the max value at column 4. you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy|||Originally posted by r937
you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy

haha..
i think i did.

But i think I got it..check it out:

SELECT
one AS one,
two AS two,
three AS three,
max(four) AS four
INTO #temp
FROM TABLE1 A
GROUP BY one, two, three;

SELECT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO ANSWERTABLE
FROM TABLE1 A INNER JOIN #temp B
ON (A.one = B.one
AND A.two = B.two
AND A.three = B.three
AND A.four = B.four);

I'm checking the results...and it looks good so far.

See any bugs or loopholes ?

Thanks again.|||bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy|||Originally posted by r937
bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy

ya thanks...
got the same results (y)

but for some reason i can only get it to work if the "yourtable zz" is a temp table.
If i directly access the "yourtable" i get the following errors:

Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.|||did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy|||Originally posted by r937
did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy

ya i named the table...
it's funny, cause it's still a correlated query if I substitute the temp table for the outer query table.|||i cannot help you if you do not show me your query

rudy|||Originally posted by r937
i cannot help you if you do not show me your query

rudy
ya sorry...
i'm legally bound not to give out any code..
i know it's anal, and doesn't really matter and the code's not important or anything, but i still can't.

But it's exactly like how u posted (i'm pretty sure i can follow that, haha)
'cept that the 'yourtable' is a view.

I ran the code in MS Access, and it was able to run. Except it did not include only one unique combinations of columns one two and three.
So i guess I wouldn't be able to use it even if I got it to go in SQLServer