help with select

• Mad-Dog

  SSCertifiable

  Points: 6108

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 9, 2011 at 2:29 am

  #1365667

  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: 6108

  More actions

  August 9, 2011 at 2:34 am

  #1365672

  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

  August 9, 2011 at 2:40 am

  #1365673

  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

  August 9, 2011 at 2:54 am

  #1365681

  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: 6108

  More actions

  August 9, 2011 at 4:27 am

  #1365733

  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: 148786

  More actions

  August 9, 2011 at 4:41 am

  #1365739

  Yes, but why?

  John

• Mad-Dog

  SSCertifiable

  Points: 6108

  More actions

  August 9, 2011 at 4:58 am

  #1365748

  i need to put the results in here

  +N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[eventtime ]),'<p class="datarowserror">Not Avail') + N'</p></TD>'

  +N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[eventid]),'<p class="datarowserror">Not Avail') + N'</p></TD>'

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  August 9, 2011 at 5:05 am

  #1365756

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

  +N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[eventid]),'<p class="datarowserror">Not Avail') + N'</p></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

  August 9, 2011 at 5:09 am

  #1365757

  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: 6108

  More actions

  August 9, 2011 at 5:35 am

  #1365781

  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-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

  August 9, 2011 at 6:19 am

  #1365825

  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

  ^{“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: 6108

  More actions

  August 9, 2011 at 6:34 am

  #1365831

  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

  August 9, 2011 at 6:39 am

  #1365835

  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: 6108

  More actions

  August 9, 2011 at 8:08 am

  #1365920

  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.