The Most Recent Record From A datetime Type Column

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

.NET Inside SQL Server (2005)

The Most Recent Record From A datetime Type Column

Author

Topic

SenneK
Starting Member

2 Posts

Posted - 2007-04-05 : 08:39:12

I got some issues regarding selecting the most recent record from a datetime type column.
The probelm consists in following:
Table: News
Needed columns: Letter [varchar]
DateTimePublication [datetime]

Table News: Letter DateTimePublication
FR1_dd_mm_yy dd.mm.yy hh:mm:ss
FR1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
FR2_dd_mm_yy dd.mm.yy hh:mm:ss
FR2_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR3_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN2_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU3_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU4_dd_mm_yy dd.mm.yy hh-2h:mm:ss

The ideea is that there are letters with the same name [Letter] but different DateTimePublication ... and I need only the letters with the most recent DateTimePublication for every letter type like FR_dd_mm_yy/EN_dd_mm_yy/DU_dd_mm_yy.

Thank you for your time and consideration!

SenneK

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 08:50:11

[code]Select Letter, max(DateTimePublication) as DateTimePublication
from news
group by Letter
Order by Letter[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SenneK
Starting Member

2 Posts

Posted - 2007-04-05 : 09:03:30

With the MAX(DateTimePublication) it's okey but I also need to display them as folows:
--day1---
FR_day1
EN_day1
DU_day1
--day2---
FR_day2
EN_day2
DU_day2
.......
--dayn--
FR_dayn
EN_dayn
DU_dayn

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-05 : 11:27:16

You need to normalize your data...that representation should be split into two separate columns

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

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 18:08:35

Use proper datatypes. It Letter column contains dates, USE DATETIME or SMALLDATETIME datatype.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources