Select query RSS

• • Reply
  

  syogod

  0 Points

  2 Posts

  Select query

  Jul 26, 2012 01:44 PM|LINK

  Hi,

  I have to do a query on a table that I've never done before and am a bit unsure how to proceed.  The table contains log entries for numerous servers, with columns: id, ProfileName, Date_Time, Message.  What I need to figure out how to do is get the latest 1,000 log entries for each server.  So for example, if there's 20 servers, I'll need to return 20,000 results.

  I know how to do it for just one server: SELECT TOP 1000 * FROM TableName WHERE ProfileName LIKE 'ServerName1'.  However, I'm not sure how to return all the servers at once and I'd hate to have to write 20+ queries to get it all seperately.  Any ideas? 

• • Reply
  

  ignatandrei

  All-Star

  134511 Points

  21576 Posts

  Moderator

  MVP

  Re: Select query

  Jul 26, 2012 01:46 PM|LINK

  first, select distinct servername in a table.

  Then join.

  My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools
  • Edited by decker dong - msft on Aug 01, 2012 03:00 AM
  • Edited by syogod on Nov 21, 2012 08:14 PM
• • Reply
  

  MattsDotNetU...

  Contributor

  3178 Points

  515 Posts

  Re: Select query

  Jul 26, 2012 01:50 PM|LINK

  You can do this with a Row_Number() like below:

  DECLARE @Temp TABLE ( id int, ProfileName varchar(max), Date_Time datetime, Message varchar(max))
  
  INSERT INTO @Temp VALUES (1, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (2, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (3, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (4, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (5, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (6, 'Server1', GetDate(), 'message');
  INSERT INTO @Temp VALUES (7, 'Server2', GetDate(), 'message');
  INSERT INTO @Temp VALUES (8, 'Server3', GetDate(), 'message');
  INSERT INTO @Temp VALUES (9, 'Server3', GetDate(), 'message');
  INSERT INTO @Temp VALUES (10,'Server4', GetDate(), 'message');
  
  SELECT *
  FROM (
  	SELECT *, Row_Number() OVER (PARTITION BY ProfileName ORDER BY Date_Time DESC) RowNumber
  	FROM @Temp
  ) DataWithRowNumber
  WHERE DataWithRowNumber.RowNumber <= 2;

• • Reply
  

  syogod

  0 Points

  2 Posts

  Re: Select query

  Jul 26, 2012 02:09 PM|LINK

  ignatandrei

  first, select distinct servername in a table.

  Then join.

  Thanks, that seems like a good way of doing it.  However, and I probably should have mentioned this at the start, I do not have the greatest experience with t-sql, I primarily stick to the administration side of SQL.  Is it possible I could get an example of how the join would work in this case?

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Select query

  Jul 28, 2012 01:48 AM|LINK

  syogod

   Is it possible I could get an example of how the join would work in this case?

  Hi,

  1）Fetching all the remote servers——you can just use SQLDMO（SQL Distributed Management Objects，and you can refer it just from "Add references……"=>"COM"）.

  2）Then please do the codes……

  SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
  
  SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
  
  List<string> serverNames = new List<name>();
  
   for(int i=0;i<sqlServers.Count;i++)
  
   {
       object srv = sqlServers.Item(i + 1);
  
       if(srv != null)
  
       {
              serverNames.Add(srv.ToString());
  
       }
  
   }
  
  //Here you should loop each of value from "serverNames" and then execute to fetch values with the help of SqlCommand

  • Edited by decker dong - msft on Aug 01, 2012 03:00 AM
  • Edited by syogod on Nov 21, 2012 08:14 PM