Sql Ranged Selecting
Jun 15, 2008How can I select data between Mth row and Nth row from a specific search condition? for example , I want to get the 5th to 10th employee who is born in March and ordered by their name.
View 1 Replies
ADVERTISEMENT
Ranged Datetime Predicates && Cardinality Estimates
Mar 27, 2006Hello all. I'm running SQL Server 2000 and I'm trying to get a veryfew, recent rows of data from a table based on an indexed datetimecolumn. Here's my predicate:where order_date > dateadd(hour, -1, getdate())i.e. everything more recent than one hour ago. This corresponds to the3 or 4 rows in which I'm interested. I have order_date indexed and Ihave current statistics. When I check the explain plan for this queryI see expected rows returned: 114,000. When I go on to join to severalother tables I end up with unnecessary hash joins -- due to theinaccurate cardinality estimates on this table.However, if I use the following predicate (which corresponds to datawithin the last 3 days):where order_date > '2006-03-24'then I see an estimated rows returned: 6 -- which is pretty accurate.[color=blue]>From there the rest of the query's explain plan falls together nicely.[/color]So my question is: how do I get the optimizer to realize that one hourago is pretty recent?Many thanks,Scott
View 2 Replies View RelatedSelecting The Most Recently Edited Item AND Selecting A Certain Type If Another Doesn't Exist
Sep 20, 2007I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
Selecting A View And Selecting FROM A View Is Wildly Different
Feb 21, 2006A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie
Selecting NOT TOP 1
Jun 10, 2004Hi,
I am trying to select all entries from a database apart from the top/latest entry, via a stored procedure.
Below is the code that i have but i am doing it wrong somehow.
Any ideas?
Thanks
CREATE PROCEDURE spNEWSARCHIVE AS
SELECT *
FROM tblNEWS
WHERE NewsID NOT TOP 1
ORDER BY NewsID DESC
GO
Selecting A Set For Min Value
Apr 14, 2006Hi,I have the following set and would like to select all rows that havemin value of column 4 for a given column 1 and 2 group, irrespective ofcolumn 3, as below:C1 C2 C3 C4---------------------A B x 5A B y 10A BB XX 55A BB YY 11AA CC z 1AA CC zz 11Need---A B x 5A BB YY 11AA CC z 1using sql server 2000 (which does not provide rank or partition by)Thanks in advance,Tamas
View 2 Replies View RelatedSDS Selecting Even Though I Cancel
Sep 5, 2006Hello,I have a gridview bound to a sqldatasource control. I have an emptydatatemplate setup. In the selecting event, I make sure that an actual select has been performed (and not on null data); however, I have cancelselectonnullparameters set to true to stop it, because initially it will be null.However, the selecting event runs, even when I cancel it, the emptydatatemplate shows up. How do I prevent that from occuring?
View 2 Replies View RelatedSelecting Nothing With SQL Statement
Jan 7, 2007Hi Folks,
You can select all fields with:SELECT * FROM [TableName]
How do you select NOTHING or NONE with a SELECT statement?
Thanks
Selecting Between Two Times
Feb 19, 2007Dear All,
I need to select records between two datetimes.
My Database Structure and sample values are as below
Fromdate ToDate
2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000
Query i used
select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)))
My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00'
Please help
Thanks
Selecting Distinct
Jul 18, 2007Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
View 1 Replies View RelatedSelecting Record
Sep 4, 2007Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
View 3 Replies View RelatedNeed Some Help On SELECTING Event
Apr 21, 2008hello all,
i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx.
private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
Do While Skip In Selecting...
Oct 21, 2004Hello:
I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?
Here is a code snippet of what is going on:
Do While varCount < varRecordCount
conSqlConnect.Open()
cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@idnumber and date<@date", conSqlConnect)
cmdSelect.Parameters.Add( "@accountnumber", "10000" )
cmdSelect.Parameters.Add( "@date", dtnow )
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
If IsDbNull(dtrdatareader("value")) = false Then
varValue = dtrdatareader("value")
End If
End While
dtrdatareader.Close()
conSqlConnect.Close()
'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
varCount = varCount + 1
LoopAny ideas?
Selecting * WHILE Converting
Jun 2, 2005I have a tbale with maybe 30 columns and I'm selecting all where a
record number matches a parameter I've passed. That works fine,
however is there any way to do some conversions to fields when
selecting without having to list out each column? For instance
this is what it looks like now:
SELECT *
FROM Losses
WHERE @AppRecord = AppRecord
ORDER BY Record ASC
But I want to convert say one column, is there a way to keep similar syntax instead of listing out each column?
SELECT *, CONVERT(varchar(15), [Losses]) as [Losses]
FROM Losses
WHERE @AppRecord = AppRecord
ORDER BY Record ASC
And the column I'm converting is of type 'money'. I'm converting
it to get rid of the extra zeroes at the end. If you know a
better way to do it I'd be interested in knowing.
Selecting The Same Field Twice
Sep 6, 2005CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_GetAllIssues]@moduleId intASSELECT PreciseData_IssueTracker_Issue.id AS 'issueId',PreciseData_IssueTracker_Status.name AS 'statusName',PreciseData_IssueTracker_Issue.subject AS 'subject',PreciseData_IssueTracker_Type.name AS 'typeName',Users.Username AS 'assignedUserName',Users.Username AS 'raiserUserName',PreciseData_IssueTracker_Issue.raiseDate AS 'raiseDate'FROMPreciseData_IssueTracker_Issue INNER JOIN PreciseData_IssueTracker_Status ON PreciseData_IssueTracker_Issue.statusId=PreciseData_IssueTracker_Status.idINNER JOIN PreciseData_IssueTracker_Type ON PreciseData_IssueTracker_Issue.typeId=PreciseData_IssueTracker_Type.idINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.assignedUserIdINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.raiserUserIdWHERE PreciseData_IssueTracker_Issue.moduleId=@moduleIdORDER BY PreciseData_IssueTracker_Issue.raiseDate DESCGOHow do i make it work?
View 1 Replies View RelatedSelecting Fields On The Fly
Sep 20, 2005Hi
I have a content management system that I am building. My CMS has a
pages table which contains 60 fields, some are varchars, some are ntext
some are dates etc. They have field names which match their purpose
like varchar1, ntext3 etc. These fields are used up depending on which
template is applied to the page.
The templatefields table holds holds the information about each field
in a template. It's control type (rich text control, date control etc)
and the field in the pages table that the data will be stored in
(varchar1,ntext3 etc)
What I want to be able to do is loop through the templatefeilds table
for a particular template and then using the field name for the pages
table, go to the pages table and get the value to populate the control.
So to clarify, I would like to produce a stored procedure which outputs
the page field name (varchar1) from the templatefields table and
varchar1's value in the pages table. Maybe in a temporary table.
Is this possible?
Any guidance would be appreciated..
Thanks in advance
Selecting A Date
Mar 23, 2006Hi --
What is the proper way to select results with a date in the where statement when the datatype of the column is datetime?
select * from table where date_field = '3-23-2006' does not get any results.
Thank-you for your help.
Please Help!!!!! Selecting Rows
Jan 7, 2002how can I select 10 random rows from a table?
Please help
Selecting Indexes
May 8, 2001We are developing an Access application that will be used to run adhoc reports against a SQL Server table. Selection criteria is entered on a form, a passthru query is built on the fly, and the results are formatted for output via an Access report.
There are 16 different fields on the query form. Two are date fields (from / to), the rest are text. The user is required to supply a date range for the report. They may specify any combination of the remaining query fields.
Examples:
From/To dates, part number, work order number
From/To dates, location, operation, sales order number, operator
What is the best scheme to use for the indexes? One index for each potential query field? Or, a single index that includes ALL query fields? Or, some other combination?
I should also mention that there will be no realtime updates to the table. On a weekly basis, we will import updates from a mainframe extract (dropping the indexes prior to the import, rebuilding them afterwards).
Selecting 5 Records
Jan 25, 2002I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
View 2 Replies View RelatedSelecting The Database Name.
Oct 10, 2000Can anyone help me in finding out the database name?
For example, @@SERVERNAME gives you the name of the server
on which you SQL Server is running.
In the same way, is there any global variable which gives me the
name of the database in the context in which I am running my queries or stored procs?
Thanks
Sushruth Nanduri.
Selecting A Single Row
Nov 16, 2001I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help...
I looked around and couldn't find anything on it.
thanks in advance
Selecting Data
Aug 21, 2001Hello .
Sometimes when i try to select data from a table (from a selected database)
using SQL Query Analyzer (for exemple) i receive an error message : "The Object XX does not exist" . But when i prefix it by the Owner it work .
What is the problem then ?
Selecting The N'th To The N'th + X Rows
Sep 12, 2001Hello,
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length.
Any ideas people?
Thanks,
Alan
Selecting Job To Run At End Of Month In EM
Oct 30, 2001I wanted to schedule a DTS package to run on the last day of the month at 11:30 PM. I noticed that when you use EM to do this I scheduled it to run monthly on the 31st. Hate to ask a dumb question, it will run tomorrow on 10/31, what happens in November on the 30th, is SQL smart enough to do it on that date?
View 2 Replies View RelatedSelecting 2 F/k's From Same Table?
Sep 13, 2005This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity
cityID (p/k)
stateID (f/k to tblState.stateID)
cityName (varchar 35)
tblStudent
studID (p/k)
cityID (f/k to tblCity.cityID)
hometownID (f/k to tblCity.cityID)
studFName (varchar 25)
studLName (varchar 35)
...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be
Quote: 12150, 127, 1050, 'John', 'Smith', ...
How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID,
s.studLName + ', ' + s.studFName AS studNameName,
c.cityName, c.cityName AS swapconNum
FROM tblStudent AS s INNER JOIN tblCity AS c
ON (s.cityID = c.cityID)
WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
RudeDog
Selecting From A List
Aug 19, 2005Hello,
I think I have a pretty simple question,
I have a list of record IDs that I need to search on. I'm trying to create a query like this:
Select record_ID
FROM table
WHERE record_id <> (1,3,4,6,8,9,11)
In a table with 12 records, I'd like it to return 2,5,6,10,12
Background:
I have a list of .jpgs that correspond to record_ids. I want to take the list of .jpg names and compare that to the record_ids so I may delete any .jpgs that do to not have a corresponding record.
Any help would be appreicated.
-Matt
Selecting One Of 3 Columns...
Dec 29, 2004Hi,
I have a table that has 3 different types of dates (date1, date2, date3), and they represent the edit times of the 3 different sections on the website.
How do I select only one of those three that's the most recent? (It would represent the most recent edit on the website overall).
It would be an easy task if it was only one column - I would simply select the MAX, but I have to be selective between 3 different columns and pick the one I "like".
Thanks,
NB
Selecting A Sub Set Of Results
Jan 17, 2008Hi hopeful this will be a quick one, i have a query that returns a hell of a lot of records not if i do top 1000 i can get the first thousand, is there a simple way to then get the second thousand and the third?
because i need to process the data but with it going all in one go it's being a bit of a resource hog.
this is best i've come up with so far but I'm hoping for a better method
SELECT TOP 10 *
FROM testtable
WHERE id NOT IN
(SELECT TOP 10 id
FROM testtable)
Selecting Nth Row Only (offset)
Jul 29, 2014I have a query which can return multiple rows. I want to select only the nth row. In other db languages, this is achieved using the OFFSET command, but in SQL Server this command does not exist. I saw that you can hack it using ROWCOUNT and an ORDER BY clause, but I don't have anything to order by. Is there some other way to do this? My query is
Code:
SELECT S.SystemName FROM Projects AS P INNER JOIN ProjectSystems AS PS ON PS.ProjectId = P.ProjectId LEFT JOIN Systems AS S ON S.SystemId = PS.SystemID WHERE P.ProjectId = @ProjectID
This will select an example result of:
Code:
System Name
System 1
Test System 2
Test 123
How can I do something like (psuedo code)
Select Row @row From query
Selecting Last 3 Characters.
Mar 10, 2004I want to select the last 3 characters from a field that have different character lenghts. How would you be able to do this.
For example:
pfw510s9055
70125033
efw674s8002
I only want the last 3 characte from each of these items.
Any help would be appreciated.
Selecting Based On The Value Of A SUM
Apr 21, 2008Okay, let's see if I can explain this one. I am summing multiple lines of data from a labor detail table, by status. Using this query
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1
I get these results...
LastnameWBS1WBS2LongnameHELDLABORTBWrittenOffLaborWRITTENOFFLABOR
Boulet0001039.000100S.r. 41 & Del Prado Shopping Center/miscellaneous civil engineering18408.6309923.47
Boulet0001039.000102S.r. 41 & Del Prado Shopping Center/rezoning process008790
Boulet0001039.000106S. R. 41 & Del Prado Shopping center / const plan rev for environ planting2200.6800
Boulet0001039.000107S.r. 41 & Del Prado Shopping Center/cpd rezoning9335.4600
Okay, so now, of coarse, I want to change everything. I only want to return rows if there is a value > 0 in either Held Labor or TBWrittenOffLabor. Otherwise, no row return.
Here's what I tried, but it didn't work out because it still returns rows, it just zero's out the values for written off labor.
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor,
SUM(CASE WHEN LD.HELD > '0' THEN LD.WrittenOff ELSE '0' END) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1
Selecting With Grouping
Mar 4, 2014I have a table and I need three columns:AdEnrollID (Foreign Key), SyStatchangeID (Primary KEY) and EffectiveDate (DateTime). I need the MAX(EffectiveDate) group by AdEnrollID but I also need the systatchangeID for that row.
here is my query:
Select AdEnrollID,SyStatChangeid, MAX(EffectiveDate)
from systatchange where EffectiveDate < '8/1/2013' and adenrollID=47964
Group by grouping sets ((SyStatChangeID, systatchange.AdEnrollID))
but it gives me a lot of results (I know, I group by systatchangeID and is going to include them all) Here is the result:
AdEnrSyStat(No column name)
479642749562012-08-07 12:44:36.787
479642772862012-09-05 09:32:02.480
479642786212012-09-13 13:45:11.840
479642791062012-09-19 14:58:33.080
479642858232012-12-07 15:14:01.087
[code]....
I am only interested in the one in bold. How can I do this.