A pesky recordset

I have the following User_Log table:
ID User Location Use_Date
1 1234 RR 1/5/05
2 1234 KT 1/10/05
3 1234 ZF 1/20/05
4 1259 ZF 1/13/05
5 1259 KT 1/19/05
I need a T-SQL statement that will get me the two user records with the most
recent Use_date. In this case it would be the fields with the IDs of 3 and
5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
date is 1/19/05. If I do a
SELECT User, max(Service Date)
FROM User_Log
GROUP BY User
I lose the location. If I include the Location in my query, I obviously get
a record for each location. So how do I get the best of both worlds;
Grouping on the user to get the max date but not losing the location of the
record that qualifies in the max statement?
Thanks,
JonSELECT user, location, use_date
FROM User_Log AS L
WHERE use_date =
(SELECT MAX(use_date)
FROM User_Log
WHERE user = L.user)
David Portas
SQL Server MVP
--|||SELECT User, Location, Use_Date
FROM User_log ul
WHERE Used_Date = (SELECT MAX(Use_Date) FROM User_log ul2
WHERE ul.User = ul2.User)
or
SELECT ul.User, ul.Location, ul.Use_Date
FROM User_log ul
INNER JOIN
(SELECT User, MAX(Use_Date) AS max_use_date FROM User_log ) ul2
ON ul.User = ul2.User AND ul.Use_date = ul2.max_use_date
Although both of them assume that the combination of User and Use_date is
unique.
--
Jacco Schalkwijk
SQL Server MVP
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:uYQavE7DFHA.3728@.TK2MSFTNGP14.phx.gbl...
>I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the
> most recent Use_date. In this case it would be the fields with the IDs of
> 3 and 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent
> service date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously
> get a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of
> the record that qualifies in the max statement?
> Thanks,
> Jon
>|||SELECT TOP 2
Id,
User,
Location,
Use_Date
FROM
Users
ORDER BY
Use_Date DESC
"Jon Westmore" wrote:

> I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the mo
st
> recent Use_date. In this case it would be the fields with the IDs of 3 an
d
> 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
> date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously g
et
> a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of th
e
> record that qualifies in the max statement?
> Thanks,
> Jon
>
>

A newbie need help for SQL XML

I am trying to set up my SQL server so that I can get recordset in XML format
over HTTP.
In a seperate IIS server (with SQL tools):
1) I created a directory (C:\nwind) and underneath created C:\nwind\template
and C:\nwind\schema.
2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
directory nwind and pointed to C:\nwind.
3) Set the security to either sql or windows.
4)Selected the SQL server and appropriate database.
5)Checked allow POST and allow URL query.
6) Created virtual names
template->type-template->location-C:\nwind\template, dbobject->type-dbobject,
schema->type-schema->location-C:\nwind\schema.
I have both a windows account and sql account "test" that has access to
northwind database. When I type the following in a workstation:
http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
I get the:
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)
But when I ran the IIS in XP machine I didn't have any problem. Any help is
greatly appreciated. Thanks
What OS is this machine running?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>I am trying to set up my SQL server so that I can get recordset in XML
>format
> over HTTP.
> In a seperate IIS server (with SQL tools):
> 1) I created a directory (C:\nwind) and underneath created
> C:\nwind\template
> and C:\nwind\schema.
> 2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
> directory nwind and pointed to C:\nwind.
> 3) Set the security to either sql or windows.
> 4)Selected the SQL server and appropriate database.
> 5)Checked allow POST and allow URL query.
> 6) Created virtual names
> template->type-template->location-C:\nwind\template,
> dbobject->type-dbobject,
> schema->type-schema->location-C:\nwind\schema.
> I have both a windows account and sql account "test" that has access to
> northwind database. When I type the following in a workstation:
> http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
> I get the:
> HTTP Error 404 - File or directory not found.
> Internet Information Services (IIS)
> But when I ran the IIS in XP machine I didn't have any problem. Any help
> is
> greatly appreciated. Thanks
|||It's IIS6.0/Windows 2003.
"Irwin Dolobowsky [MSFT]" wrote:

> What OS is this machine running?
> --
> Thanks,
> Irwin
> Irwin Dolobowsky
> Program Manager, SqlXml
> http://blogs.msdn.com/irwando
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SN" <SN@.discussions.microsoft.com> wrote in message
> news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>
>
|||Check the docs, there are some steps for getting it running on Win2k3. The
"Guidelines and Limitations" section.
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:23043A59-00DB-464F-A58B-504E3EAE751E@.microsoft.com...[vbcol=seagreen]
> It's IIS6.0/Windows 2003.
> "Irwin Dolobowsky [MSFT]" wrote: