How can a SQL statement be written to return a specified range of
rows? For example:
-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.
Select * from tblContact -- Return only rows 5 through 10
Thanks
Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
Hi.How does one return a range of rows.I know that "Top 5" will return rows 0 - 5but, how do I get 6 - 10?thanks
View 17 Replies View RelatedHello,John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me anidea how to achieve paging in sql server without row numberfunctionality. Thank you John. The following works for me, not veryeficient though:SELECT * FROM( SELECT top 5 * FROM( SELECT top 10 * FROM( SELECT top 10 *FROM dft_documentORDER BY documentkey ASC) aORDER BY documentkey DESC) b) dORDER BY documentKey ASCThe innermost SELECT gives 10 rows out of which last 5 needed.regards
View 2 Replies View RelatedI have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
Is there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?
View 10 Replies View RelatedI currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates
@MonthFrom int,
@YearFrom int,
@MonthTo int,
@YearTo int,
@DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)
And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
Given in a record in from a Table called WorkSchedule:
idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8
I need to return for each record in the WorkSchedule Table
1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006
8 8 8 8..................8
Please help.
Thank you.
-Robert
I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc
Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc
I'm using MSSQL 2005
tks
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View 1 Replies View RelatedUsing MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS (
SELECT PQIM.PQPageID
,PQIM.PageURL as PageDescription
,CONVERT(Date,NCPI.RequestDateTime) AS [Date]
,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR]
,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount
FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
[Code] ....
This is the output I get :
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54
[Code] ....
But I am expecting
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
In SQL server I have the column doTableDate set a Datetime.I need extract all rows in on date range and I think use to syntax `Between And`If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...
Code:
SELECT
*
FROM
doTable
WHERE
doTableDate BETWEEN CONVERT (
[code]....
Hello Friends,
Is there any way in sql server to get records with in a particular range. Lets say that we got 100 records satisfying a query, I want only first 20 records or records in the range 40 - 60. Kindly suggest me how to accomplish this in sqlserver and if possible provide a code snippet regarding this. Thanks in advance.
Regards,
Ch.Praveen.
Hello all, thanks in advance for any help you might be able to give.
I'm familiar with the Top command but I need something else to help in a project I'm working on.
I would like to select rows 1 through 100000 from a specific table in one query then 100001 through 200000 in a second query and 200001 through 300000 in a third and so on until I have gone through all rows. There happens to be 424000 in the table I'm working on.
Any help is appreciated.
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies View RelatedIs it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies View RelatedHI All,
I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
CREATE procedure dbo.ph_getphonebookoption
@country nvarchar(100),
@company nvarchar(100),
@office nvarchar(100)
as
select
Ext,
FName,
LName,
Title
from
phonebook
where country =@country
and company=@company
and office =@office
select count (*)
GO
Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
What other options do I have?
Thanks,
John
is there is any way to find out whether my query return 0 rows inside a stored procedure.
View 2 Replies View RelatedHi All,
I have a question,
Select top 10 * from employee
the above statement return top 10 row.
but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
Thanks for the help
Thanks
Bhaskar
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server
Thanks
Are there any way to execute a procedure and return N random rows?