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 empty values in a column

Hello,
I am trying to replicate somthing I did in my access reports.
I need to count how many 0 dollar values or null values show up in a
combination of two colums.
I dont know where to begin in Reporting Services. In Acces this was VBA.
Here is the VBA CODE.
End If
If Nz(Me.sQuotedPrice, 0) = "0" Or Len(Me!sQuotedPrice) = 0 And
Nz(Me.sGuessPrice, 0) = "0" Or Len(Me!sGuessPrice) = 0 Then
sJobsMissingPrices = sJobsMissingPrices + 1
End If
I try this in Reporting Services and it says it is not with in the correct
scope?
Public Shared Function GetEmptyValues()
Dim JobsMissingPrices
If ReportItem!JobPrice.Value= 0 Or Len(ReportItems!JobPrice.value) = 0 And
NzReportItems!Guess_Price = "0" Or Len(ReportItems!Guess_Price) = 0 Then
JobsMissingPrices = JobsMissingPrices + 1
End If
End Function
Any help would be appreciated.
--
Thank You, LeoHi Leo,
It seems there is something wrong here, an additional "Nz" was not removed
NzReportItems!Guess_Price = "0"
If removing Nz doesn't resolve this issue, please provide more detailed
information about this two values and I would like to make a reproduce on
my side.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Leo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Filters in combination with the aggregate function

Hi all,
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?