Need Query - 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

General SQL Server Forums

New to SQL Server Programming

Need Query

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2011-05-19 : 16:56:33

Hello Sir ,

This is my table data

date ConsultantID ConsultantStatusID
2011-05-20 01:39:30.840 19205 3
2011-05-20 01:34:10.617 19206 3
2011-05-20 01:34:58.547 19207 3
2011-05-20 04:16:22.963 19207 12
2011-05-20 01:33:42.543 19208 3
2011-05-20 04:16:22.963 19208 12
2011-05-20 01:33:12.283 19210 3
2011-05-20 04:16:22.963 19210 12

I want to fetch only that record whose date is maximum according to consultantid wise .

Please help me out sir

Yaman

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 17:01:21

;WITH a(Date, ConsultantID, ConsultantStatusID, rn) AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ConsultantID ORDER BY Date DESC) FROM myTable)
SELECT Date, ConsultantID, ConsultantStatusID FROM a
WHERE rn=1

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 17:20:53

SELECT * FROM myTable o
WHERE EXIST (
SELECT * FROM myTable i
WHERE i.ConsultantID = o.ConsultantID
GROUP BY i.ConsultantID
HAVING MAX(i.[fdate]) = o.[date])

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 17:21:41

which performs better?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2011-05-19 : 17:39:44

quote:
Originally posted by robvolk

;WITH a(Date, ConsultantID, ConsultantStatusID, rn) AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ConsultantID ORDER BY Date DESC) FROM myTable)
SELECT Date, ConsultantID, ConsultantStatusID FROM a
WHERE rn=1

Thanks Alot Sir ,
I am able to fetch data but i am not able insert query data in to another table .

INSERT INTO teat1(CID,ConsultantStatus,StatusComment,StatusDate)

WITH a( ConsultantID, ConsultantStatusID,StatusComment, Date,rn) AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ConsultantID ORDER BY Date DESC) FROM test)
SELECT ConsultantID, ConsultantStatusID,StatusComment,Date FROM a
WHERE rn=1

Please help me out Sir .

Yaman

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 17:41:43

WITH a( ConsultantID, ConsultantStatusID,StatusComment, Date,rn) AS (
INSERT INTO teat1(CID,ConsultantStatus,StatusComment,StatusDate)
SELECT *, ROW_NUMBER() OVER (PARTITION BY ConsultantID ORDER BY Date DESC) FROM test)
SELECT ConsultantID, ConsultantStatusID,StatusComment,Date FROM a
WHERE rn=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources