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

Find max entry in table

I would like to select the highest id in a table for each computer record.

example data:
ID Computer name
24 computer1
23 computer1
22 computer2
24 computer3

The max id is not going to be the same for every computer.

When the script below is run, I receive the highest number only. All IDs with 24 will be listed. I need all the computer names and the highest ID they are associated with. In the case above, 24 computer1 should be on the list and 23 computer1 should not.

Thanks,

Script...

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
where s.id_snapshot = (Select MAX (id_snapshot)
From servers)Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||mpereziii, your query should work too ... Do you have any issues?

Originally posted by rdjabarov
Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||With the original query mpereziii was getting only rows where id_snapshot was equal to the maximum value in the table.|||Thanks for the response.
I adjusted my script with your suggestion. I received too many rows. 424,000 rows and it searched for 18sec.

The query returned every id_snapshot for every server_name (computer name).

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid|||How about this:

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select server_name, sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid and s.server_name = maxsid.server_name|||Thank you sjp. The query now works very well. I really appreciate everyones input. Thank you.