Max () query problem

Say I have a table like this (just for example)

ID Date Name
__ ____ _____
1 1/2/2004 Store A
2 1/1/2004 Store A
3 1/3/2004 Store B
4 1/2/2004 Store B

Say I wanted to get the rows for the maximum dates for stores A and B
aka this record set..

ID Date Name
__ ____ _____
1 1/2/2004 Store A
3 1/3/2004 Store B

How would I got about doing this in sql?

thanks

-JimSELECT id, date, name
FROM SomeTable AS T
WHERE date =
(SELECT MAX(date)
FROM SomeTable
WHERE name = T.name)

--
David Portas
SQL Server MVP
--

Matrix Drill down Value not Correct

I am making a report that has column groups Day of Week and then "late".
(early late ontime) Row groups consist of region,district,store,dept. The
value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
numbers throughout the drilldown. I am having problems with the Ratio value
calculated as format(sum( Fields!Measures_Rec_Count.Value
,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
"matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
level but remains the same throughout the drilldown...I have tried using the
InScope() function but not getting any positive results....
Any Ideas?
Sunday Monday
...etc
Early ontime late Early ontime
late
region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
district 1 3(83%) 2(17%)
district 2 4(83%) (17%)
district 3 3(83%) (17%)still could not get dynamicly grouped columns and rows to drill down with the
correct numbers....altered my MDX to create a matrix with one dynamic column
and 3 static columns and the drill down works now.....If anyone knows of a
way to have multiple dynamic columns where calculated cells drill down with
correct values please let me know.
Thanks.
"Raz" wrote:
> I am making a report that has column groups Day of Week and then "late".
> (early late ontime) Row groups consist of region,district,store,dept. The
> value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
> numbers throughout the drilldown. I am having problems with the Ratio value
> calculated as format(sum( Fields!Measures_Rec_Count.Value
> ,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
> "matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
> level but remains the same throughout the drilldown...I have tried using the
> InScope() function but not getting any positive results....
> Any Ideas?
> Sunday Monday
> ...etc
> Early ontime late Early ontime
> late
> region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
> district 1 3(83%) 2(17%)
> district 2 4(83%) (17%)
> district 3 3(83%) (17%)
>

Matrix Drill Down Issue

I have a matrix with dynamic columns and rows. If I apply any filters to the
row groups then the higher levels only reflect the first "store" result set
in the higher levels instead of the aggregate total. If I remove the filters
on each group then the values at each level are correct but my first
[member_caption] at each level is null.
Expression: =Fields!location_Division.Value="" operator: = value: =false
Thanks in advance for your help.
col1 col2 col3 col4 col5 col6
division 5 5 4 4 5 4
region 5 5 4 4 5 4
district 1 5 5 4 4 5 4
store 10 5 5 4 4 5 4
store 12 3 1 2 3 2 3Found a way around this problem....have to select the leaves in your MDX
and sum the column value......
"Raz" wrote:
> I have a matrix with dynamic columns and rows. If I apply any filters to the
> row groups then the higher levels only reflect the first "store" result set
> in the higher levels instead of the aggregate total. If I remove the filters
> on each group then the values at each level are correct but my first
> [member_caption] at each level is null.
> Expression: =Fields!location_Division.Value="" operator: = value: =false
> Thanks in advance for your help.
> col1 col2 col3 col4 col5 col6
> division 5 5 4 4 5 4
> region 5 5 4 4 5 4
> district 1 5 5 4 4 5 4
> store 10 5 5 4 4 5 4
> store 12 3 1 2 3 2 3

Materialized views

Hello,
Do you know how can i create a materialized view in sqlserver like oracle.
Because i want to store in a table the result of a query.You can create a view of the query you have and then create a clustered index on that view. This will make SQL Server store the data physically.|||This smells more like a simple SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_03_8nad.asp) to me.

-PatP