Query "hightest value" contents of each "folder"?

Hi,

Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:

CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]

This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?

Thanks for any help you can give me,

RobinAnother way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group...23b16be2b945c0f

David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

QA Template Mystery

In query analyzer I can not see the templates, clicking on the various
folders does nothing. The tql files DO exist in the templates folder and I
have double checked that QA is looking at the correct folder. I can use the
templates from the menu, but I just can not see them in the folder tree. Any
ideas on how to fix this annoying problem?
Files are hidden or something like that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:B0EA84C1-D862-42CA-8C92-9E727982F187@.microsoft.com...
> In query analyzer I can not see the templates, clicking on the various
> folders does nothing. The tql files DO exist in the templates folder and I
> have double checked that QA is looking at the correct folder. I can use the
> templates from the menu, but I just can not see them in the folder tree. Any
> ideas on how to fix this annoying problem?
|||Not hidden, but compressed. Thanks for the idea to look at the other
attributes. This has been driving me crazy.
"Tibor Karaszi" wrote:

> Files are hidden or something like that?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dean" <Dean@.discussions.microsoft.com> wrote in message
> news:B0EA84C1-D862-42CA-8C92-9E727982F187@.microsoft.com...
>
>

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/a...th-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
http://www.sqlserver2005.de
--

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
--
http://www.sqlserver2005.de
--

Q: reporthPath

Hello,
I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I set
my ReportPath for report viewer to see it in my APS.NET application.
Thanks,Are you using RS 2000 or 2005.
If 2005 are you doing local or server mode?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> Hello,
> I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> set
> my ReportPath for report viewer to see it in my APS.NET application.
> Thanks,
>|||RS 2000. Can I use RS2005 in my SQL Server 2000?
"Bruce L-C [MVP]" wrote:
> Are you using RS 2000 or 2005.
> If 2005 are you doing local or server mode?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> > Hello,
> >
> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> > set
> > my ReportPath for report viewer to see it in my APS.NET application.
> > Thanks,
> >
> >
>
>|||The reason I asked is if you are using 2000 then you have a misconception.
You say that your reports are in a particular folder. That statement makes
no sense with 2000. Report RDL files exist in the development environment
(which is no your web server). Then you deploy to RS. The act of deploying
has RS stored the reports in the database. No rdl files exist on the server.
Then to integrate with your app you use URL integration or soap (web
services). The report viewer is an example on how to do this. I believe it
uses URL integration.
VS 2005 has a webform and winform controls. Highly functional versions that
use web services rather than URL integration. These controls require RS
2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
still have to have a 2005 license.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> RS 2000. Can I use RS2005 in my SQL Server 2000?
> "Bruce L-C [MVP]" wrote:
>> Are you using RS 2000 or 2005.
>> If 2005 are you doing local or server mode?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> > Hello,
>> >
>> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
>> > I
>> > set
>> > my ReportPath for report viewer to see it in my APS.NET application.
>> > Thanks,
>> >
>> >
>>|||Hi Bruce,
Thank you very much for your response. This part is a little bit confusing
for me. I know I can set ServerUrl and ReportPath properties to render the
report.
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/RepName";
This was working. Now I moved all the report under a folder called â'Reportsâ'
at the same level of RepName folder. So I am assuming
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/Reports/RepName";
this should work. How ever I get error rsItemNotFound. What am I missing?
Thanks,
"Bruce L-C [MVP]" wrote:
> The reason I asked is if you are using 2000 then you have a misconception.
> You say that your reports are in a particular folder. That statement makes
> no sense with 2000. Report RDL files exist in the development environment
> (which is no your web server). Then you deploy to RS. The act of deploying
> has RS stored the reports in the database. No rdl files exist on the server.
> Then to integrate with your app you use URL integration or soap (web
> services). The report viewer is an example on how to do this. I believe it
> uses URL integration.
> VS 2005 has a webform and winform controls. Highly functional versions that
> use web services rather than URL integration. These controls require RS
> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
> still have to have a 2005 license.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> > RS 2000. Can I use RS2005 in my SQL Server 2000?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Are you using RS 2000 or 2005.
> >>
> >> If 2005 are you doing local or server mode?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> >> > Hello,
> >> >
> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
> >> > I
> >> > set
> >> > my ReportPath for report viewer to see it in my APS.NET application.
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >>
>
>|||How did you move the reports. The only way I know that works for this is to
deploy into another folder. Did you go through a deploy process?
I think what you need to do is to first get everything organized and check
that it is working with Report Manager first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B79A3821-90E3-4C7E-8527-D175E43FC6BA@.microsoft.com...
> Hi Bruce,
> Thank you very much for your response. This part is a little bit confusing
> for me. I know I can set ServerUrl and ReportPath properties to render the
> report.
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/RepName";
> This was working. Now I moved all the report under a folder called
> "Reports"
> at the same level of RepName folder. So I am assuming
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/Reports/RepName";
> this should work. How ever I get error rsItemNotFound. What am I missing?
> Thanks,
>
> "Bruce L-C [MVP]" wrote:
>> The reason I asked is if you are using 2000 then you have a
>> misconception.
>> You say that your reports are in a particular folder. That statement
>> makes
>> no sense with 2000. Report RDL files exist in the development environment
>> (which is no your web server). Then you deploy to RS. The act of
>> deploying
>> has RS stored the reports in the database. No rdl files exist on the
>> server.
>> Then to integrate with your app you use URL integration or soap (web
>> services). The report viewer is an example on how to do this. I believe
>> it
>> uses URL integration.
>> VS 2005 has a webform and winform controls. Highly functional versions
>> that
>> use web services rather than URL integration. These controls require RS
>> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but
>> you
>> still have to have a 2005 license.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
>> > RS 2000. Can I use RS2005 in my SQL Server 2000?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Are you using RS 2000 or 2005.
>> >>
>> >> If 2005 are you doing local or server mode?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how
>> >> > should
>> >> > I
>> >> > set
>> >> > my ReportPath for report viewer to see it in my APS.NET application.
>> >> > Thanks,
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>