Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2010-03-25 : 10:44:10
|
| the below code works in a query, but not in a view?...how can I implement this for general use by my data analysts?The code below joins several tables, groups the records by student ID, ranks them so that only the most current record for each student is selected for the query... I would like this query to be used by my data analysts (who generally use excel as a front end to the sql database, using excel data connectors)...but I cannot save this as a view (I provide different views that do the primary slice and dice for the analysts)....select * from(select a.[num-idoc],rank() over (partition by a.[num-idoc] order by a.extractdate desc)as newest from studentdirectory a left join Housing b on a.[num-idoc]=b.[num-idoc] left join Classes c on a.[num-idoc]=c.[num-idoc]) zwhere newest = 1thanks for the help... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-25 : 11:15:48
|
| why doesnt it work in a view? did you try wrapping this insideCREATE VIEW viewnameAS...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-25 : 11:21:27
|
| Any chance of some sample data?There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-25 : 11:55:30
|
| that worked just fine!..I was using the add view utility...pasting the sql code in the code window...when I saved I got a warning windowquery Definations Differ-The following errors were encountered while pasing the contents of the SQL pane...- The OVER SQL construct or statement is not supported.- the query cannot be represented graphically in the Diagram and Criteria Paneby using the power-user method...the view was added and works just peachy..thank you!footnote... I looked closer, realized the pop-up was a WARNING not an error...I hit the IGNORE button on the pop up..and it did create the view...we learn something new every day, eh?... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-25 : 12:08:07
|
quote:
Originally posted by dlorenc
that worked just fine!..I was using the add view utility...pasting the sql code in the code window...when I saved I got a warning windowquery Definations Differ-The following errors were encountered while pasing the contents of the SQL pane...- The OVER SQL construct or statement is not supported.- the query cannot be represented graphically in the Diagram and Criteria Paneby using the power-user method...the view was added and works just peachy..thank you!footnote... I looked closer, realized the pop-up was a WARNING not an error...I hit the IGNORE button on the pop up..and it did create the view...we learn something new every day, eh?...
yeah...i guessed you used view editor. it gives warnings like that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-25 : 12:28:17
|
| not to beat a dead horse..but...WHY?.. does it give me a warning that rank is not supported..when it is?...mmm?...this is a retorical question...I dont expect anyone from microsoft to respond on why they did something..*smile* |
|
|
|
|
|
|
|
|
rank cant be used in a view?