selecting max date to return one row

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

selecting max date to return one row

Author

Topic

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-10 : 12:40:45

I have a query that runs and returns multiple rows of data. all the data in the rows is the same, except for the date field in which i want the latest date to be the only row returned.

Currently:

computer number date user
CLBLCW9143 TAC22649 2005-07-28 14:34:48.000 gdzi
CLBLCW9143 TAC22649 2005-07-28 15:10:32.000 gdzi
CLBLCW9143 TAC22649 2005-08-01 14:48:30.000 gdzi

Wanted: Only return the row with the latest date

computername req_number DateActioned NT_ID
CLBLCW9143 TAC22649 2005-08-01 14:48:30.000 gdzi

Code:

select distinct l.ComputerName, a.Req_number,
/*(select top 1 a.DateApprovalActioned from tactrack..vwlocaladmin_tacdetails a, tactrack..tblLocalUsers l
where l.computername=a.computername and l.nt_id=a.ntid
order by a.DateApprovalActioned desc)
as DateActioned,*/
a.DateApprovalActioned as DateActioned,
l.NT_ID

from tactrack..tblLocalUsers l, tactrack..vwlocaladmin_tacdetails a

where
(l.nt_id=a.ntid) and l.computername=a.computername

group by l.ComputerName, a.Req_number, a.DateApprovalActioned, l.nt_id

________________________________
uncommenting that section of code only returns the latest date for the first entry in all rows.

Example:

computername req_number DateActioned NT_ID
CLBLCW9143 TAC22649 2005-08-09 17:54:55.000 gdzi
CLBLCW9598 TAC22650 2005-08-09 17:54:55.000 jmoun

___________________________
i'm pretty sure this is a simple fix with the select top 1 * or selecting the max somehow, but i am not sure exactly how to fix this so it works. Any help would be apperciated. Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 12:45:05

You are on the right track. Use a TOP 1 with an ORDER BY.

Consider this example:

declare @table table (yourID int identity(1,1), yourdate datetime)
insert into @table
	select getdate() - 1 union select getdate()-2 union select getdate()-3

select * from @table

select top 1 yourID, yourDate
from @table
order by yourDate desc

Nathan Skerl

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-10 : 12:52:43

doing a top 1 with an order by returns the same date for every row.
_______________________________________________________________________
select distinct l.ComputerName, a.Req_number,
(select top 1 a.DateApprovalActioned from tactrack..vwlocaladmin_tacdetails a, tactrack..tblLocalUsers l
where l.computername=a.computername and l.nt_id=a.ntid
order by a.DateApprovalActioned desc)
as DateActioned,
l.NT_ID

from tactrack..tblLocalUsers l, tactrack..vwlocaladmin_tacdetails a

where
(l.nt_id=a.ntid) and l.computername=a.computername

group by l.ComputerName, a.Req_number, a.DateApprovalActioned, l.nt_id
__________________________________________

I'm trying to turn this into an update statement so i really need to be able to map the one row

CLBLCW914368 TAC2264974 2005-08-09 17:54:55.000 gdziad
CLBLCW959879 TAC2265001 2005-08-09 17:54:55.000 jmounce
CLBLCW960637 TAC2265017 2005-08-09 17:54:55.000 faslam
CLBLCW961713 TAC2265019 2005-08-09 17:54:55.000 mawright
CLBLCW961721 TAC2265002 2005-08-09 17:54:55.000 nidas
CLBLCW961722 TAC2265020 2005-08-09 17:54:55.000 tpe
DNACPLAL3881 TAC2210940 2005-08-09 17:54:55.000 sgalyk

_______________________________________________

Without the select top 1 part, i get 3 rows returned for each different date (see below), all of which are different from the other computer/req_number/user combinations. i need the max date for each computer/req_number/user combination as i am trying to turn this into an update statement eventually and will need to be able to update with just the latest date.

CLBLCW914368 TAC2264974 2005-07-28 14:34:48.000 gdziad
CLBLCW914368 TAC2264974 2005-07-28 15:10:32.000 gdziad
CLBLCW914368 TAC2264974 2005-08-01 14:48:30.000 gdziad
CLBLCW914368 TAC2264974 2005-08-01 15:05:42.000 gdziad
CLBLCW959879 TAC2265001 2005-07-28 14:27:46.000 jmounce
CLBLCW959879 TAC2265001 2005-08-02 13:06:27.000 jmounce
CLBLCW959879 TAC2265001 2005-08-02 13:06:28.000 jmounce
CLBLCW960637 TAC2265017 2005-07-29 08:17:25.000 faslam
CLBLCW960637 TAC2265017 2005-08-03 12:33:17.000 faslam
CLBLCW960637 TAC2265017 2005-08-03 12:33:18.000 faslam

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 13:07:23

Can you please post the DDL for the tables involved in the query?

Thanks!

Nathan Skerl

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-10 : 13:20:30

tactrack..tblLocalUsers l

computername varchar(50)
req_number varchar(100)
dateactioned datetime(8)
nt_id (50)
______________________________________________

tactrack..vwlocaladmin_tacdetails a

computername varchar(50)
req_number varchar(100)
ntid varchar(50)
dateapprovalactioned datetime(8)

those are the only columns i care about from the 2 tables. When i do the update, i want to update tactrack..tblLocalUsers (l) columns req_number and DateActioned, from data in tactrack..vwlocaladmin_tacdetails (a) where the computernames and ntid's match up. I would like to use the record with the latest date in tactrack..vwlocaladmin_tacdetails for the DateActioned column

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 13:57:35

Ahh, I see what youre getting at.

Check this one:

declare @tblLocalUsers table (computerName varchar(50), req_number varchar(100), dateactioned datetime, nt_id varchar(50))
insert into @tblLocalUsers
	select 'CLBLCW914368', 'TAC2264974', '2005-07-28 14:34:48.000', 'gdziad' union
	select 'CLBLCW959879', 'TAC2265001', '2005-07-28 14:27:46.000', 'jmounce'

declare @vwlocaladmin_tacdetails table (computername varchar(50), req_number varchar(100), nt_id varchar(50), dateapprovalactioned datetime)
insert into @vwlocaladmin_tacdetails
	select 'CLBLCW914368', 'TAC2264978', 'gdziad', '2005-07-28 14:34:48.000' union
	select 'CLBLCW914368', 'TAC2264976', 'gdziad', '2005-07-28 12:34:48.000' union
	select 'CLBLCW914368', 'TAC2264974', 'gdziad', '2005-07-28 10:34:48.000' union
	select 'CLBLCW914368', 'TAC2264972', 'gdziad', '2005-07-28 8:34:48.000' union
	select 'CLBLCW914368', 'TAC2264970', 'gdziad', '2005-07-28 6:34:48.000' union

	select 'CLBLCW959879', 'TAC2264979', 'jmounce', '2005-07-28 15:34:48.000' union
	select 'CLBLCW959879', 'TAC2264977', 'jmounce', '2005-07-28 13:34:48.000' union
	select 'CLBLCW959879', 'TAC2264975', 'jmounce', '2005-07-28 11:34:48.000' union
	select 'CLBLCW959879', 'TAC2264973', 'jmounce', '2005-07-28 9:34:48.000' union
	select 'CLBLCW959879', 'TAC2264971', 'jmounce', '2005-07-28 7:34:48.000'

-- select * from @tblLocalUsers
-- select * from @vwlocaladmin_tacdetails

select v.computerName, v.req_number, v.nt_id, v.dateapprovalactioned
from @vwlocaladmin_tacdetails v 
inner join (	select	computerName,
			max(dateapprovalactioned) as dateapprovalactioned
		from @vwlocaladmin_tacdetails
		group by computerName) d
on v.computerName = d.computerName 
and v.dateapprovalactioned = d.dateapprovalactioned

Nathan Skerl

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-10 : 14:23:49

i think that will work, however i have to complicate things... my vwlocaladmin is a view... not a table... so the server is unhappy:

Server: Msg 4425, Level 16, State 1, Line 8
Cannot specify outer join operators in a query containing joined tables. View or function 'tactrack..vwlocaladmin_tacdetails' contains outer join operators.

should i turn the view into a new table to make this work?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 14:45:34

I think you just need to update the syntax in the view to SQL-92 style. Sounds like you are using old school join syntax:

for example:

WHERE table_1.column_a =* table_2.column_a

I dont think you can mix them in the same query.

From BOL:
"Although the outer join operators from earlier versions of SQL Server are supported, you cannot use both outer join operators and SQL-92-style joined tables in the same FROM clause."

If you need help updating the syntax post the code for the view.

Nathan Skerl

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-10 : 15:35:30

i just made a new table, it was easier that way. however i am now getting no data.
Query is as follows
________________________________________________________________
select distinct a.computerName, a.NTID , a.Req_number,
a.DateApprovalActioned as DateActioned

from tactrack..tbllocaladmin_tacdetails a

inner join ( select ComputerName, nt_id,
max(DateActioned) as dateActioned
from tactrack..tblLocalUsers
group by computerName,nt_id) l
on l.computername=a.computername

where l.nt_id=a.ntid
__________________

Edit: seems my new table is not populating. Query for that one is:

truncate table tblLocalAdmin_TACdetails
update tblLocalAdmin_TACdetails
set Application= v.Application, CreateDate= v.CreateDate,ComputerName=v.ComputerName,
HRID=v.HRID, NTID=v.NTID,displayname=v.displayname, REQ_NUMBER=v.REQ_NUMBER, ASSIGNEE_LOGIN=v.ASSIGNEE_LOGIN,
ASSIGNEE_FULLNAME=v.ASSIGNEE_FULLNAME, Approver_Role=v.Approver_Role,LastTacStatus=v.LastTacStatus,
DATEAPPROVALACTIONED=v.DATEAPPROVALACTIONED
from

vwLocalAdmin_TACdetails v

______________

help???

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 15:39:26

No no! No need to put that data in another table when it already exists elsewhere. Is this a one-time thing? If not, lets try updating the syntax first.

You should be using the standard syntax anyways.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 16:23:49

Not sure what happened to your view code (I swear it was up here a second ago).

Here is a quick example of how you would update the 3rd union clause of the view to ansi-92 syntax:

select distinct 
	s.obj_name Application, 
	s.CreateDate, 
	case charindex('.',s.App_USERID )
		when 0 then App_USERID
		when -1 then App_USERID
		else substring(App_USERID,1, charindex('.',s.App_USERID )-1)
	end ComputerName, 
	HRID, 
	t.ntid NTID, 
	t.displayname, 
	isnull(REQ_NUMBER, 'TAC REQUEST NOT FOUND') 
	REQ_NUMBER, 
	isnull(ASSIGNEE_LOGIN,'-') ASSIGNEE_LOGIN, 
	isnull(ASSIGNEE_FULLNAME ,'-') ASSIGNEE_FULLNAME, 
	isnull(ROLE,'-') Approver_Role,
	isnull(STATUS,'-') LastTacStatus, 
	DATEAPPROVALACTIONED DATEAPPROVALACTIONED
from tacmssql..tblbtac_app_status s 
	left outer join tacmssql..tblbtac_req_approval r on ltrim(rtrim(r.user_account)) = ltrim(rtrim(s.app_userid)) 
	inner join tacmssql..vwtac_users t on s.hrid = t.employeeid 
where s.obj_name = 'Local Admin Europe'
	and r.obj_name = 'Local Admin Europe'
	and r.user_account <> 'TBA'
	and s.app_status = 'enabled'
	and s.USR_DISABLE_status = 'enabled'

Please read these links for some cautions before doing this:

[url]http://www.microsoft.com/sql/techinfo/tips/development/july23.mspx[/url]

and this one also, especially the section (and code samples) on outer join changes:

[url]http://www.windowsitpro.com/SQLServer/Article/ArticleID/5658/5658.html[/url]

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources