help with select

Question

help with select

Mad-Dog

SSCertifiable

Points: 6117
More actions
August 9, 2011 at 12:47 am

#241813

Hi,
i've a table with 2 columns one log_event_num (int) and one log_event_time (datetime).
how i can write a select that will show me every day only one log_event_num per event?
THX

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Mad-Dog (8/9/2011)
Hi,
i've a table with 2 columns one log_event_num (int) and one log_event_time (datetime).
how i can write a select that will show me every day only one log_event_num per event?
THX

Do you mean "only one log_event_time per event"? If so, then which one?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

the table hold a lot of events from the same event per day.

i need to extract only one event per event per day from every event.

THX

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

How about a little sample data, say 10 rows split across two day boundaries?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

with cte as (

select ROW_NUMBER () Over (partition by (convert(varchar(256),eventtime ,103)) , eventid order by (convert(varchar(256),eventtime ,103) )) as rowid ,

convert(varchar(256),eventtime ,103) as dates , eventid from eventtb

)

select * from cte where rowid = 1

Remove event id and tweak as required .

Jayanth Kurup[/url]

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 5

this syntax give me the right results but can it be change some how to start with a select first and not with "with cte as"?

THX

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

John Mitchell-245523

SSC Guru

Points: 148809

August 9, 2011 at 4:41 am

#1365739

Yes, but why?

John

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 7

i need to put the results in here

+N'<TD>' + ISNULL( + '' + CONVERT(NVARCHAR(MAX),[eventtime ]),'Not Avail') + N'</TD>'

+N'<TD>' + ISNULL( + '' + CONVERT(NVARCHAR(MAX),[eventid]),'Not Avail') + N'</TD>'

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

OK, so instead of saying SELECT *, say SELECT N'<TD>' + ISNULL( + '' + CONVERT(NVARCHAR(MAX),[eventtime ]),'Not Avail') + N'</TD>'

+N'<TD>' + ISNULL( + '' + CONVERT(NVARCHAR(MAX),[eventid]),'Not Avail') + N'</TD>'

That's if I'm understanding you correctly. If you provide table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements and expected results, we wouldn't have to guess, and you'd get a tested solution.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Mad-Dog (8/9/2011)
this syntax give me the right results but can it be change some how to start with a select first and not with "with cte as"?
THX

As John says, Yes - a simple SELECT with GROUP BY. But we can't test without sample data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 10

some sample data from table,i need to extract with select

LOG_NUMBER,EVENT_TIME

002,2011-08-07 20:29:05.787

002,2011-08-06 20:29:05.787

002,2011-08-05 20:29:05.787

002,2011-08-05 21:29:05.787

003,2011-08-05 21:29:05.787

003,2011-08-05 20:29:05.787

004,2011-08-08 20:29:05.787

004,2011-08-08 23:29:05.787

005,2011-08-06 20:29:05.787

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Chooes the column you want, min or max (or average, if you wish):

DROP TABLE #Sample

CREATE TABLE #Sample (LOG_NUMBER CHAR(3), EVENT_TIME DATETIME)

INSERT INTO #Sample (LOG_NUMBER, EVENT_TIME)

SELECT '002','2011-08-07 20:29:05.787' UNION ALL

SELECT '002','2011-08-06 20:29:05.787' UNION ALL

SELECT '002','2011-08-05 20:29:05.787' UNION ALL

SELECT '002','2011-08-05 21:29:05.787' UNION ALL

SELECT '003','2011-08-05 21:29:05.787' UNION ALL

SELECT '003','2011-08-05 20:29:05.787' UNION ALL

SELECT '004','2011-08-08 20:29:05.787' UNION ALL

SELECT '004','2011-08-08 23:29:05.787' UNION ALL

SELECT '005','2011-08-06 20:29:05.787'

SELECT LOG_NUMBER,

MAX_EVENT_TIME = MAX(EVENT_TIME),

MIN_EVENT_TIME = MIN(EVENT_TIME)

FROM #Sample

GROUP BY LOG_NUMBER

ORDER BY LOG_NUMBER

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 12

ChrisM@Work (8/9/2011)
Chooes the column you want, min or max (or average, if you wish):
DROP TABLE #Sample
CREATE TABLE #Sample (LOG_NUMBER CHAR(3), EVENT_TIME DATETIME)
INSERT INTO #Sample (LOG_NUMBER, EVENT_TIME)
SELECT '002','2011-08-07 20:29:05.787' UNION ALL
SELECT '002','2011-08-07 20:29:05.787' UNION ALL
SELECT '002','2011-08-06 20:29:05.787' UNION ALL
SELECT '002','2011-08-05 20:29:05.787' UNION ALL
SELECT '002','2011-08-05 21:29:05.787' UNION ALL
SELECT '003','2011-08-05 21:29:05.787' UNION ALL
SELECT '003','2011-08-05 20:29:05.787' UNION ALL
SELECT '004','2011-08-08 20:29:05.787' UNION ALL
SELECT '004','2011-08-08 23:29:05.787' UNION ALL
SELECT '005','2011-08-06 20:29:05.787'
SELECT LOG_NUMBER,
MAX_EVENT_TIME = MAX(EVENT_TIME),
MIN_EVENT_TIME = MIN(EVENT_TIME)
FROM #Sample
GROUP BY LOG_NUMBER
ORDER BY LOG_NUMBER

it's not give me the correct results.

LOG_NUMBER '002' needs to be

00205/08/2011

00206/08/2011

00207/08/2011

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Mad-Dog (8/9/2011)
...
it's not give me the correct results.
LOG_NUMBER '002' needs to be
00205/08/2011
00206/08/2011
00207/08/2011

Apologies. Actually it should appear three times:

SELECT LOG_NUMBER,

MAX_EVENT_TIME = MAX(EVENT_TIME),

MIN_EVENT_TIME = MIN(EVENT_TIME)

FROM #Sample

GROUP BY LOG_NUMBER, DATEDIFF(dd,0,EVENT_TIME)

ORDER BY LOG_NUMBER

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 14

ChrisM@Work (8/9/2011)
Mad-Dog (8/9/2011)
...
it's not give me the correct results.
LOG_NUMBER '002' needs to be
00205/08/2011
00206/08/2011
00207/08/2011
Apologies. Actually it should appear three times:
SELECT LOG_NUMBER,
MAX_EVENT_TIME = MAX(EVENT_TIME),
MIN_EVENT_TIME = MIN(EVENT_TIME)
FROM #Sample
GROUP BY LOG_NUMBER, DATEDIFF(dd,0,EVENT_TIME)
ORDER BY LOG_NUMBER

Thanks it is working good.