1. Registered User
Join Date
Sep 2003
Posts
102

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

(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);

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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 think
Code:
```select one, two, three, max(four)
from yourtable
group by one, two, three```
GROUP 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/

3. Registered User
Join Date
Sep 2003
Posts
102

## believe it or not George isn't at home..

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.

But i need to be able to get entries from almost all 8 columns (ie. other columns which do not have to be distinct).

4. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447

## Re: believe it or not George isn't at home..

Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()

5. Registered User
Join Date
Sep 2003
Posts
102

## Re: believe it or not George isn't at home..

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.

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

## Re: believe it or not George isn't at home..

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
Code:
```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

7. Registered User
Join Date
Sep 2003
Posts
102

## Re: believe it or not George isn't at home..

Originally posted by r937
you shoulda said it like that in the first place
Code:
```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
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.

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

9. Registered User
Join Date
Sep 2003
Posts
102
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.

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

11. Registered User
Join Date
Sep 2003
Posts
102
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.

12. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

rudy

13. Registered User
Join Date
Sep 2003
Posts
102
Originally posted by r937

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•