April 7, 2011 at 5:15 am
Hi,
table test has following data.
APP_ID APP_Status APP_Date
321 Receive 20-Mar-2010
321 Pend 21-Mar-2010
321 Decline 22-Mar-2010
678 Receive 07-May-2010
678 Assess 08-May-2010
678 Approve 09-May-2010
678 Attoney 10-May_2010
I want to select max date for each app_id.
I tried the following:-
select * from Test where APP_Date in (select max(APP_Date) from test where APP_ID in (select distinct APP_ID from test))
it returns on one date
please help
April 7, 2011 at 5:30 am
Is this what you're after?
select APP_ID, MAX(APP_DATE) from Test
group by APP_ID
April 7, 2011 at 5:35 am
Hi,
when I include the app_status field in my select statement then in return all the status for that app_id, while I'm only looking for the status related to the max date.
April 7, 2011 at 5:41 am
Ok. I think I see what you're after - DDL and expected result would be useful in the future as people can then give you tested code, but something like this:
;
WITH MAX_DATE_CTE
AS ( SELECT APP_ID ,
APP_Status ,
App_Date ,
ROW_NUMBER() OVER ( PARTITION BY APP_ID ORDER BY APP_DATE DESC ) AS ROWNUMBER
FROM Test
)
SELECT APP_ID ,
APP_Status ,
App_date
FROM MAX_DATE_CTE
WHERE ROWNUMBER = 1
You should look up Common Table Expressions and the Row_Number in BOL to understand the workings of this
April 7, 2011 at 5:45 am
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY lastwaittype ORDER BY login_time ) AS TieBreaker
, *
FROM
sys.sysprocesses
) dta
WHERE
dta.TieBreaker = 1
April 7, 2011 at 5:49 am
That's the magic right there Howard thank you very much. It worked.
April 7, 2011 at 6:04 am
Hi,
Can I use it to get Min date as well?
April 7, 2011 at 6:08 am
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY lastwaittype ORDER BY login_time ) AS TieBreaker_max
, ROW_NUMBER() OVER ( PARTITION BY lastwaittype ORDER BY login_time DESC ) AS TieBreaker_min
, *
FROM
sys.sysprocesses
) dta
WHERE
1 IN (dta.TieBreaker_max, dta.TieBreaker_min)
April 7, 2011 at 6:16 am
Sorted, thanks guys
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply