By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,111 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,111 IT Pros & Developers. It's quick & easy.

searching for latest date and time only

P: n/a
jas
> This is what I want to do with the data table below.

I only want it to show one id for each issue. ( ie, 4001 only once,
4002 only once, etc. But only the one with the latest date and time)

Ie. 4001 should only be in the table once, with the latest date -
which should be :
4001 09/12/2003 17:12:09 (as I only want to show the last time
the id was updated - do you get me now )

Ie. 4002 should show only 4002 11/12/2003 15:25:13


id hs_change_date hs_change_time
4001 27/10/2003 10:38:27
4001 09/12/2003 14:43:58
4001 09/12/2003 17:12:09
4002 27/10/2003 10:56:28
4002 09/12/2003 14:44:11
4002 11/12/2003 15:25:13
4003 27/10/2003 11:13:12
4003 09/12/2003 14:44:21
4003 10/12/2003 10:48:02
4003 10/12/2003 13:25:09
4004 27/10/2003 11:28:09
4004 09/12/2003 14:44:29
4004 09/12/2003 17:18:28
4005 27/10/2003 15:55:40
4005 28/10/2003 10:18:24
4006 27/10/2003 15:59:47
4006 28/10/2003 10:18:38
4006 09/12/2003 14:44:40
4006 09/12/2003 16:47:15

Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
What data types are you using here? SQLServer DATETIME stores both data and
time in the same column so I don't understand why you appear to have
separate columns. It helps to clarify your requirements if you include DDL
with your questions.

Assuming you define your table with a single DATETIME column you can get the
result you require quite easily:

CREATE TABLE Sometable (id INTEGER, hs_change_dt DATETIME, PRIMARY KEY /*
??? */ (id, hs_change_dt))

SELECT id, MAX(hs_change_dt) AS hs_change_dt
FROM Sometable
GROUP BY id

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

P: n/a
jas
The database is already setup with two separate columns - one for
date and one for time. see below
I only want it to show one id for each issue. ( ie, 4001 only once,
4002 only once, etc. But only the one with the latest date and time)

Ie. 4001 should only be in the table once, with the latest date -
which should be :
4001 09/12/2003 17:12:09 (as I only want to show the last time
the id was updated - do you get me now )

Ie. 4002 should show only 4002 11/12/2003 15:25:13

Do you get me?
id hs_change_date hs_change_time
4001 27/10/2003 10:38:27
4001 09/12/2003 14:43:58
4001 09/12/2003 17:12:09
4002 27/10/2003 10:56:28

"David Portas" <RE****************************@acm.org> wrote in message news:<7_********************@giganews.com>... What data types are you using here? SQLServer DATETIME stores both data and
time in the same column so I don't understand why you appear to have
separate columns. It helps to clarify your requirements if you include DDL
with your questions.

Assuming you define your table with a single DATETIME column you can get the
result you require quite easily:

CREATE TABLE Sometable (id INTEGER, hs_change_dt DATETIME, PRIMARY KEY /*
??? */ (id, hs_change_dt))

SELECT id, MAX(hs_change_dt) AS hs_change_dt
FROM Sometable
GROUP BY id

Jul 20 '05 #3

P: n/a
> The database is already setup with two separate columns - one for
date and one for time. see below


OK, but what are the data types? You still haven't told us and it makes a
difference to the solution. I'll assume you have DATETIME for the date and
CHAR(8) for the time.

CREATE TABLE Sometable (id INTEGER, hs_change_date DATETIME, hs_change_time
CHAR(8) NOT NULL, PRIMARY KEY (id, hs_change_date, hs_change_time))

SELECT id,
CAST(DATEDIFF(D,0,hs_change_dt) AS DATETIME) AS hs_change_date,
CONVERT(CHAR(8),hs_change_dt,108) AS hs_change_time
FROM
(SELECT id,
MAX(CAST(CONVERT(CHAR(11),hs_change_date,126)+
hs_change_time AS DATETIME))
FROM Sometable
GROUP BY id) AS T (id,hs_change_dt)

This seems like a pointless design that wastes at least 8 bytes of storage
per row and worst of all it makes the data very difficult to maipulate (see
above and compare it with my first answer).

--
David Portas
SQL Server MVP
--
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.