help me to solve - 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 2008 Forums

Transact-SQL (2008)

help me to solve

Author

Topic

taghipour
Starting Member

5 Posts

Posted - 2012-08-26 : 16:32:36

How i can get red colored records completely? i tried get it with grouping but i couldn't, cause i need all fields. i want records that have max(Date) for ID. i tried

SELECT ID, MAX(Date) AS Last_Date
FROM Practicing
GROUP BY ID

this sql command gave me ID and Date Columns but i want Cnt and status columns too

ID | Date | Cnt | Status

32 | 2012-08-20 | 1 | True
32 | 2012-08-21 | 2 | True
32 | 2012-08-25 | 3 | True
33 | 2012-08-22 | 1 | True
33 | 2012-08-25 | 1 | True
34 | 2012-08-20 | 1 | True
34 | 2012-08-23 | 1 | false
34 | 2012-08-24 | 1 | false
34 | 2012-08-25 | 2 | True
34 | 2012-08-26 | 3 | True

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-26 : 16:48:37

SELECT select ID,Date,Cnt,Status
FROM

(select ID,Date,Cnt,Status
,rank() over (partition by ID order by date desc) as LastDate
from practicing
group by ID
) t1
WHERE lastDate = 1

Jim

Everyday I learn something that somebody else already knew

taghipour
Starting Member

5 Posts

Posted - 2012-08-26 : 17:46:33

quote:
Originally posted by jimf

SELECT select ID,Date,Cnt,Status
FROM

(select ID,Date,Cnt,Status
,rank() over (partition by ID order by date desc) as LastDate
from practicing
group by ID
) t1
WHERE lastDate = 1

Jim

Everyday I learn something that somebody else already knew

Thank you for help. its working but without "group by ID"
best solution

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-26 : 17:51:57

you're right, I threw that group by in there since i was originally going to do just

SELECT p.ID,p.Date,p.Cnt,p.Status
FROM
Practicing p
INNER JOIN
(select ID,max(Date) as LastDate
from practicing
group by ID
) t1

ON p.id = t1.id and p.date = t1.lastdate

Jim

Everyday I learn something that somebody else already knew

taghipour
Starting Member

5 Posts

Posted - 2012-08-27 : 05:28:00

Thank you Jim you saved my time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources