SQL Server – Execute Query Returning Maximum 100 Records

sql server

I have a table in which every record has an ID, a date and a value.
There are a lot of records, and I want to get those dates and values, to create a graph. But I do not want to have more than 100 points.

What I need to do, is if for example there are 300 records, one for every day, to execute a query which will take 100 records, but uniformly. I mean if I have for example

23, 1 January 2014, 5
34, 2 January 2014, 54
45, 3 January 2014, 35
56, 4 January 2014, 55
67, 5 January 2014, 35
78, 6 January 2014, 56
89, 7 January 2014, 53
90, 8 January 2014, 55
94, 9 January 2014, 57

For example, If I wanted 3 results maximum, my query should return for example the record for 1 January 2014, 4 January 2014 and 7 January 2014. How is it possible to do that? I cannot think anything…

Best Answer

You could assign a row number to your results, then show every Xth record from that result. You do this by calculating the total number of records, and dividing that by your desired number of intervals. If the modulo is 0, spit out that record.

In my example below, I want to 10 records (approx) from the sysobjects table, sorted alphabetically by name. The result is 10 records.

This was a lot harder to explain than to write.

;with SummaryTable as
(
select row_number() over (order by name) as RowNum,
       name,
       (select count(*) from sys.objects) as 'TotalCount'
 from sys.objects
)
select *
  from SummaryTable 
 where RowNum % (TotalCount / 10)  = 0

Related Solutions

Sql-server – Fixing a query that returns failed job information

This is a little scrappy, but it is a start...

I have extracted the Schedule ID from the [last_outcome_message] field, and joined it to the Schedule ID. That should give you the correct result set

I could not find anywhere else where the schedule_id is stored against a History Event.

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message         
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id

     AND CONVERT(INT, LTRIM(RTRIM(REPLACE(LEFT(last_outcome_message, 
                   PATINDEX('% (%', last_outcome_message)), 
                   'The job failed.  The Job was invoked by Schedule ', ''))))
          = sjc.schedule_id

LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE 
run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC

SQL Server – How to Find All Circular References in Self-Referencing Table

This calls for a recursive CTE:

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0;

See it in action here: SQL Fiddle

Update:

Added distance to be able to exclude all self cycles (see ypercube's comment):

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0
  AND R.Distance > 0;

SQL Fiddle

Which one you should use depends on your requirement.

Best Answer

Related Solutions

Sql-server – Fixing a query that returns failed job information

SQL Server – How to Find All Circular References in Self-Referencing Table

Related Question