Need Help!!!!! - Data manipulation

• Agile

  Ten Centuries

  Points: 1153

  More actions

  January 30, 2009 at 1:22 am

  #77247

  Good day to all,

  Im having a hard time doing this T-SQL, can anyone help me with this one,, please...:(

  Im doing this for 3 hours.

  This is the given data:

  UsersDateTime Sections Title

  Client1 2009-01-30 08:00 AM I A

  Client2 2009-01-30 09:00 AM I B

  Client3 2009-01-30 09:30 AM I B

  This is the required output:

  Data Section Title TotalRows

  Client3 2009-01-30 09:30 AM I B 3

  I must get the latest and count the number of rows.

  Thanks,
  Morris

• Dugi

  SSCoach

  Points: 17998

  More actions

  January 30, 2009 at 3:55 am

  #935380

  this is the little code and the situation like yours I didn't have yet and sure that the others can have better idea how to do that but try and just to see if you retrieve the correct results!

  SELECT TOP 1 *, ROW_NUMBER() OVER (ORDER BY DATECOLUMN) AS COUNT_ROW FROM YOUR_TABLE

  ORDER BY DATECOLUMN DESC

  hope it helps!

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• Dugi

  SSCoach

  Points: 17998

  More actions

  January 30, 2009 at 4:40 am

  #935398

  Just to be sure that this code above is just to find you solution here, as you can see I didn't count nothing just generating the number in order by date with Row_number() function and selecting the last record!

  :hehe::hehe:

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 30, 2009 at 4:59 am

  #935406

  Try this...

  DROP TABLE #Temp

  CREATE TABLE #Temp (Users VARCHAR(10), [DateTime] DATETIME, Sections VARCHAR(1), Title VARCHAR(1))

  INSERT INTO #Temp (Users, [DateTime], Sections, Title)

  SELECT 'Client1', '2009-01-30 08:00 AM', 'I', 'A' UNION ALL

  SELECT 'Client2', '2009-01-30 09:00 AM', 'I', 'B' UNION ALL

  SELECT 'Client3', '2009-01-30 09:30 AM', 'I', 'B'

  SELECT TOP 1 *, [Total Rows] = (SELECT COUNT(*) FROM #Temp)

  FROM #Temp

  ORDER BY [DateTime] DESC

  Cheers

  ChrisM

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

• Dugi

  SSCoach

  Points: 17998

  More actions

  January 30, 2009 at 5:16 am

  #935417

  Chris Morris (1/30/2009)

  ---

  Try this...

  DROP TABLE #Temp

  CREATE TABLE #Temp (Users VARCHAR(10), [DateTime] DATETIME, Sections VARCHAR(1), Title VARCHAR(1))

  INSERT INTO #Temp (Users, [DateTime], Sections, Title)

  SELECT 'Client1', '2009-01-30 08:00 AM', 'I', 'A' UNION ALL

  SELECT 'Client2', '2009-01-30 09:00 AM', 'I', 'B' UNION ALL

  SELECT 'Client3', '2009-01-30 09:30 AM', 'I', 'B'

  SELECT TOP 1 *, [Total Rows] = (SELECT COUNT(*) FROM #Temp)

  FROM #Temp

  ORDER BY [DateTime] DESC

  Cheers

  ChrisM

  Yea this is correct thnx Chris Morris!

  Sorry I didn't check that we are in SQL Server 2000 forum!

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]