asked on

Selecting Last Record

I have tried to use Max in my query to pull the last record. But I think it's not working because it is only limiting to last for that user. How can I pull the last record in the table by ad and subregion?

select key2 as AdID, key3 as SubregionID,
(CASE WHEN SUBSTRING(CONVERT(varchar(12),ra.action_time,108),1,2) >= '12' THEN
CAST(CONVERT(varchar(12),ra.action_time,101) as datetime) ELSE
CAST(CONVERT(varchar(12),DATEADD(Day, -1,ra.action_time),101) as datetime) END) as ShiftDate
,r.type2
,username
,type
,max(ra.action_time) as ActionDate

from resources r

inner join resource_action ra
ON r.id = ra.resource_id

inner join messages m
ON ra.message_id = m.id

inner join users u
ON ra.user_id = u.id

where (username like 'gp0%' or username like 'ybrant0%' )
and r.key3 <>0
and detail like 'Enqueue%'
and type in ('FD_Command')
and (ra.action_time between (CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)-1)
and (CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)-0))

group by key2, key3,(CASE WHEN SUBSTRING(CONVERT(varchar(12),ra.action_time,108),1,2) >= '12' THEN
CAST(CONVERT(varchar(12),ra.action_time,101) as datetime) ELSE
CAST(CONVERT(varchar(12),DATEADD(Day, -1,ra.action_time),101) as datetime) END) ,r.type2 ,username ,type

wittyslogan

select top 1*
from table
order by column desc

Travidia

ASKER

what's that??

Scott Pletcher

You've asked for help, and I can tell you the general technique is to add a derived table that finds the max values you need, then inner join that derived table to the main query.

However, I can't help more than that because there's no way for me to know for most columns which table they come from, so it's impossible for me to properly determine the query to add.

Frankly when four different tables are joined and prefixes aren't used on *all* columns, it's just too difficult and time-consuming to figure out which columns go with which tables, since I have absolutely no knowledge of any kind about your tables.

Travidia

ASKER

that's a reasonable assesment. Here is the updated query...

select r.key2 as AdID, r.key3 as SubregionID,
(CASE WHEN SUBSTRING(CONVERT(varchar(12),ra.action_time,108),1,2) >= '12' THEN
CAST(CONVERT(varchar(12),ra.action_time,101) as datetime) ELSE
CAST(CONVERT(varchar(12),DATEADD(Day, -1,ra.action_time),101) as datetime) END) as ShiftDate
,r.type2
,u.username
,m.type
,max(ra.action_time) as ActionDate

from resources r

inner join resource_action ra
ON r.id = ra.resource_id

inner join messages m
ON ra.message_id = m.id

inner join users u
ON ra.user_id = u.id

where (username like 'gp0%' or username like 'ybrant0%' )
and r.key3 <>0
and ra.detail like 'Enqueue%'
and m.type in ('FD_Command')
and (ra.action_time between (CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)-1)
and (CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)-0))

group by key2, key3,(CASE WHEN SUBSTRING(CONVERT(varchar(12),ra.action_time,108),1,2) >= '12' THEN
CAST(CONVERT(varchar(12),ra.action_time,101) as datetime) ELSE
CAST(CONVERT(varchar(12),DATEADD(Day, -1,ra.action_time),101) as datetime) END) ,r.type2 ,username ,type

Travidia

ASKER

Any hope for me??? :-)

ASKER CERTIFIED SOLUTION

jorbroni

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial