Returning A Specified Range Of Records
Jul 14, 2005
Hi,
I have a SQL question which I suspect is very easy to answer but can't seem to find one for.
I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.
So, the SP would for example accept the parameters
@Upperbound = 80
@Lowerbound = 61
and would return a recordset of 20 records being the 61st thru 80th record from the queried table.
Thanks for the help
Guy
PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.
View 4 Replies
ADVERTISEMENT
Jan 25, 2005
Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:
SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC
Where this statement would return only records 2000 to 5000 of the returned results.
View 2 Replies
View Related
Oct 31, 2013
This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:
PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 01/12/2012 01/12/2013 arr_56464
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494
And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.
But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:
PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494
But can't seem to get this using a self join query like this:
select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID
from arrivals a INNER JOIN arrivals b
ON b.personID = a.personID
WHERE
a.arrivalid != b.arrivalid
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0
View 4 Replies
View Related
Oct 19, 2006
Hi all,I am using the following stored procedure in SQL 7 to return a range of records.CREATE PROCEDURE spRetMyTable@maxRows int, @lastRecord int, @SortPhrase varchar(122)ASbeginDECLARE @stSql VARCHAR(255) SET ROWCOUNT @maxRowsSET @stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @lastRecord) + 'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'exec(@stSql)set rowcount 0endTo execute the above procedure I am issuing the command:execute spRetMyTable @maxRows 10, @lastRecord 1500The column [colPrimary] is the primary field in the table.I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.Is there any performance overhead in the above method in the first place?Could it be improved by any alternative process?I need YOUR ASSISTANCE to cater to the above issues in a feasible way.Thanks in advance.
View 2 Replies
View Related
Jun 11, 2014
I have to find the records with in a paricular dates(from date and to date). In some cases @FromDate or @ToDate could be null. in the following query, when i am passing values @FromDate and @ToDate and execute the SP i am getting records which are not in the given range.
SELECT * FROM TABLE P WHERE
CONVERT(VARCHAR(10), P.[FromDate], 101) BETWEEN
CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10),@minFromDate, 101)
ELSE CONVERT(VARCHAR(10), @FromDate, 101) END
AND CASE WHEN @ToDate IS NULL Then CONVERT(VARCHAR(10), @maxToDate, 101)
[Code] .....
View 4 Replies
View Related
Dec 14, 2007
I currently use this query to select 4000 contacts from our database:
SELECT TOP 4000 * FROM wce_contact
where website not like ''
and expressemail like '%@%'
and idstatus like ''
and salutation not like '%there%'
and task not like '%x%'
So I use this query to send out emails, then I want the next set of contacts, so I am obv after the contacts 4001 to 8000. How would I right this into the above query?
Hope it all makes sense?
JT
View 2 Replies
View Related
Mar 1, 2008
Is there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?
View 10 Replies
View Related
Feb 12, 2005
I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).
I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?
I can use TOP 10 to get the first set of records for the first page eg.
SELECT TOP 10* FROM Suppliers
...but how do I get between 11 and 20, 21 and 30 and so on?
I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.
Someone gave me the syntax..
SELECT *
FROM employees
LIMIT 10,10;
..but it didn't seem to work.
Thanks for any help!!
View 14 Replies
View Related
Apr 16, 2004
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.
View 5 Replies
View Related
May 11, 2015
I have a column in the table which contains below values
Range
XXXX100
xxxx101
xxxx102
...
xxxx1020
below values are strings. I tried using below query but it does not return all values in the range.
Select * from table where Range Between 'XXXX100' AND 'xxxx1020'
May be in need to extract the number to display the values in given range.
View 4 Replies
View Related
Dec 7, 2006
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
"SELECT TOP 20 to 40 * FROM Northwind"
Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ
View 2 Replies
View Related
Feb 13, 2007
My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?
View 4 Replies
View Related
Feb 27, 2002
Any information as to how to handle this?
Thanks.
View 1 Replies
View Related
Nov 17, 2006
Following Proc is returning all records.I have passed any value to parameters but no luck.
CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;
if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO
zx
View 2 Replies
View Related
Jul 20, 2005
I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike
View 3 Replies
View Related
May 15, 2008
Hello -
I'm trying to write a select statement that will return only "billable" records.
A record is billable if:
- it is the only record for a Case (Case is an FKey ID column in my records table)
OR
- the last billable record for Case X has a DateTime > 24 hours before the record in question.
Getting records for the first condition is easy:
SELECT ID FROM Records r
LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case]
WHERE r2.[Case] IS NULL
It's the second part I'm having trouble with.
UNION
SELECT ID FROM Records
JOIN ?
....
WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??
I have to assume there can be any number of records for a case.
Thanks in advance!
View 10 Replies
View Related
Sep 27, 2007
HI,
If i have the following data
Code Block
Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);
Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
thanks.
View 9 Replies
View Related
Aug 18, 2006
I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.
Thanks
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0,
CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS'
WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS'
ELSE 'NIGHTS'
END)
END
IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END
END
END
SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription,
dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift
FROM dbo.LineFaultDescription INNER JOIN
dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND
dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID
and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')
WHERE (dbo.LineFaultData.AssetID = {Asset_ID})
AND (dbo.LineFaultData.Shift = '{@Shift}')
ORDER BY dbo.LineFaultData.StartTime DESC
View 4 Replies
View Related
Dec 3, 2007
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 Related
Mar 5, 2007
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
View 14 Replies
View Related
Aug 19, 2013
I want to get all records that have a valid date range for todays date(20130819).
All records have a date_f and date_t. I need to check that against todays date. The below code is my version of sql pseudo code.
SELECT DISTINCT
p.id,
p.name,
c.ip_number
FROM
tbl_Person AS p, tbl_current_conn_ipnumber AS c
[Code] .....
View 2 Replies
View Related
Oct 14, 2015
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
View 7 Replies
View Related
Nov 13, 2006
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields.
SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR"
SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue)
I have more code to show, if this looks correct. Just let me know.
Thanks in advance.
View 2 Replies
View Related
Nov 23, 2007
Hi
I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1
The SQL I have written however returns all records
SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2
WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1">
Please could someone help
Many thanks Colin
View 5 Replies
View Related
Jul 8, 2004
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
View 2 Replies
View Related
Jul 10, 2014
Here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
View 9 Replies
View Related
Mar 6, 2008
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159
View 3 Replies
View Related
May 8, 2008
Hi All,
Let's say I have a SELECT that returns 1000 records and I want them unordered or randomized How would I do that?
So instead of:
OrderID
1
2
3
4
5...and so on
I want:
OrderID
67
300
100
1
910..totally random, but all 1000 records must still be returned.
Regards
Melt
View 10 Replies
View Related
May 4, 2007
I'm trying to fully utilize parameters on a report.
I have 3 parameters, a start date, end date, and a cause.
The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.
Should I be using Filter, Report Parameters, or some other means to solve this?
Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.
Thanx!
TR
View 8 Replies
View Related
Jan 15, 2012
If you have a table that has records which are applicable over a date range, is there a preferred design for dealing with the dates?
A simple example might be an employee table, which might have multiple employees, with each employee have multiple records, each record being applicable over a particular date range.
For instance:
Code:
EmpID EmployeeName StartDate EndDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011 8/10/2011
00001 Jones, Jerry 8/11/2011 1/7/2012
00001 Jones, Jerry 1/8/2011 12/31/3000
00002 Fredricks, Fred 8/3/2011 10/15/2011
00002 Fredricks, Fred 10/16/2011 12/31/3000
One could model this table with an implied EndDate (of 12/31/3000), as in:
Code:
EmpID EmployeeName StartDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011
00001 Jones, Jerry 8/11/2011
00001 Jones, Jerry 1/8/2011
00002 Fredricks, Fred 8/3/2011
00002 Fredricks, Fred 10/16/2011
Or, one could imply the beginning date and store the first record's beginning date elsewhere, in a date hired table, or in additional field on each record. As in:
Code:
EmpID EmployeeName EndDate HireDate AdditionalFieldsOfData
00001 Jones, Jerry 8/10/2011 4/6/2011
00001 Jones, Jerry 1/7/2012 4/6/2011
00001 Jones, Jerry 12/31/3000 4/6/2011
00002 Fredricks, Fred 10/15/2011 8/3/2011
00002 Fredricks, Fred 12/31/3000 8/3/2011
View 4 Replies
View Related
Aug 21, 2014
I have two tables, customers and appointments.
I want to bulk insert records for a date range and a day of the week.
For example, John Smith has an appointment on every monday for the next three months. How do I accomplish this?
View 1 Replies
View Related
Mar 11, 2008
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008'
@EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
View 3 Replies
View Related
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
