query help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

pvsramu
Starting Member

17 Posts

Posted - 2009-07-20 : 11:01:04

I have a table like this...

mobilenumber activationdate status
12345 01/01/2009 Pending
12345 01/04/2009 Pending
12345 04/06/2009 Active
12345 04/10/2009 Active
56789 12/01/2008 Pending
56789 03/02/2009 Pending

I need the output like this:

mobilenumber activationdate status
12345 04/10/2009 Active
56789 03/02/2009 Pending

Need to retrieve the latest acvitation date and the status of the mobilenumber in the table. What is the best way to do it?

Thanks,
Ramu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 11:07:42

select mobilenumber, activationdate, status
from
(select mobilenumber, activationdate, status,row_number() over (partion by mobilenumber order by activationdate desc) as row_num from your_table
) as t
where row_num=1

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-20 : 11:27:48

[code]CREATE TABLE #temp(ID INT IDENTITY(1,1),CODE INT, DATE DATETIME, STATUS VARCHAR(8))

INSERT INTO #TEMP

SELECT '12345', '01/01/2009', 'Pending' UNION ALL
SELECT '12345', '01/04/2009', 'Pending' UNION ALL
SELECT '12345', '04/06/2009', 'Active' UNION ALL
SELECT '12345', '04/10/2009', 'Active' UNION ALL
SELECT '56789', '12/01/2008', 'Pending' UNION ALL
SELECT '56789', '03/02/2009', 'Pending'

SELECT *
FROM #temp
WHERE DATE IN
(SELECT MAX(DATE)
FROM #TEMP
GROUP BY CODE
)
[/code]

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 02:44:58

quote:

Originally posted by rajdaksha

CREATE TABLE #temp(ID INT IDENTITY(1,1),CODE INT, DATE DATETIME, STATUS VARCHAR(8))

INSERT INTO #TEMP

SELECT '12345', '01/01/2009', 'Pending' UNION ALL 
SELECT '12345', '01/04/2009', 'Pending' UNION ALL 
SELECT '12345', '04/06/2009', 'Active' UNION ALL 
SELECT '12345', '04/10/2009', 'Active' UNION ALL 
SELECT '56789', '12/01/2008', 'Pending' UNION ALL 
SELECT '56789', '03/02/2009', 'Pending'


SELECT *
FROM   #temp
WHERE  DATE IN
       (SELECT  MAX(DATE)
       FROM     #TEMP
       GROUP BY CODE
       )

-------------------------
R..

This is not reliable
Check with this

INSERT INTO #TEMP

SELECT '12345', '03/02/2009', 'Pending' UNION ALL
SELECT '12345', '01/04/2009', 'Pending' UNION ALL
SELECT '12345', '04/06/2009', 'Active' UNION ALL
SELECT '12345', '04/10/2009', 'Active' UNION ALL
SELECT '56789', '12/01/2008', 'Pending' UNION ALL
SELECT '56789', '03/02/2009', 'Pending'

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-21 : 04:03:23

hi madhi

bcoz of date field it's not reliable.
but am not clear can you please explain for the same...

-------------------------
R..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-21 : 05:01:20

try this too


select t.code,t.date,k.status
from #temp k
inner join 
(select code,max(date)as date from #temp  group by code)t on t.date = k.date and t.code = k.code

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 05:26:37

quote:
Originally posted by rajdaksha

hi madhi

bcoz of date field it's not reliable.
but am not clear can you please explain for the same...

-------------------------
R..

You have to match with code along with max(date). See bklr's query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources