Retrieve colomn with max date - 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)

Retrieve colomn with max date

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2010-03-01 : 19:03:43

I'm very new and trying to work on a problem.

I got a table with 3 columns
city_id
comment_date
comments

Need to retrieve the comment for each city with the earliest date. Below is what I'm working with but I might be totally off base.

select city_id, comment_date, comments
from resident_comment
where comment_date = (
select min(comment_date)
from resident_comment
group by city_id
)

John O

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-01 : 22:48:18

Try it:

Declare @Resident_Comment Table
(City_Id int,
Comment_DAte datetime,
Comments varchar(8000))

Insert into @Resident_Comment Values (1 ,'2010-01-01','First comment on 1st jan')
Insert into @Resident_Comment Values (1 ,'2010-02-01','First comment on 1st Feb')
Insert into @Resident_Comment Values (2 ,'2010-02-01','First comment on 1st Feb for second city')
Insert into @Resident_Comment Values (2 ,'2010-02-15','Second comment on 15th Feb for second city')
Insert into @Resident_Comment Values (3 ,'2010-02-01','First comment on 1st Feb for Third city')

select city_id, comment_date, comments
from @resident_comment RC
where comment_date = (
select min(comment_date)
from @resident_comment RC1 where RC1.City_id = RC.City_id
)

Regards,
Bohra

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 23:13:11

[code]SELECT city_id, comment_date, comments,d.id
FROM resident_comment
JOIN (SELECT CAST(city_id AS VARCHAR) + CAST(MIN(comment_date) AS VARCHAR)
FROM resident_comment
GROUP BY city_id) D (id)
ON CAST(city_id AS VARCHAR) + CAST(comment_date AS VARCHAR) = D.id;

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 02:16:42

quote:

Originally posted by ms65g

SELECT city_id, comment_date, comments,d.id
  FROM resident_comment
  JOIN (SELECT CAST(city_id AS VARCHAR) + CAST(MIN(comment_date) AS VARCHAR)
          FROM resident_comment
         GROUP BY city_id) D (id)
    ON CAST(city_id AS VARCHAR) + CAST(comment_date AS VARCHAR) = D.id;

1 Why are you concatenating ID with DATE? Index will not be used
2 When converting to VARCHAR, you should always specify the length
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 05:31:38

@Madhi
Then it will be OK?

SELECT city_id, comment_date, comments,d.id
  FROM resident_comment
  JOIN (SELECT city_id, MIN(comment_date)
          FROM resident_comment
         GROUP BY city_id) D (id, [date])
    ON city_id = d.id 
   AND comment_date = d.[date]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-02 : 05:57:01

try like this too


SELECT city_id,comment_date,comments FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY city_id ORDER BY Comment_Date ) AS rid,
* FROM @resident_comment)s WHERE rid = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 06:23:35

quote:

Originally posted by ms65g

@Madhi
Then it will be OK?

SELECT city_id, comment_date, comments,d.id
  FROM resident_comment
  JOIN (SELECT city_id, MIN(comment_date)
          FROM resident_comment
         GROUP BY city_id) D (id, [date])
    ON city_id = d.id 
   AND comment_date = d.[date]

Yes. This is better

Madhivanan

Failing to plan is Planning to fail

justjohno
Starting Member

23 Posts

Posted - 2010-03-02 : 10:14:20

Thank you everyone for your assistance. Your help gives me direction.

John O

justjohno
Starting Member

23 Posts

Posted - 2010-03-02 : 14:18:42

SELECT city_id, comment_date, comments,d.id
FROM resident_comment
JOIN (SELECT city_id, MIN(comment_date)
FROM resident_comment
GROUP BY city_id) D (id, [date])
ON city_id = d.id
AND comment_date = d.[date]

This did the trick...thank you so much

John O

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 15:02:32

quote:
Originally posted by justjohno

SELECT city_id, comment_date, comments,d.id
FROM resident_comment
JOIN (SELECT city_id, MIN(comment_date)
FROM resident_comment
GROUP BY city_id) D (id, [date])
ON city_id = d.id
AND comment_date = d.[date]

This did the trick...thank you so much

John O

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources