Hi,
I have the following set and would like to select all rows that have
min value of column 4 for a given column 1 and 2 group, irrespective of
column 3, as below:
C1 C2 C3 C4
---------------------
A B x 5
A B y 10
A BB XX 55
A BB YY 11
AA CC z 1
AA CC zz 11
Need
---
A B x 5
A BB YY 11
AA CC z 1
using sql server 2000 (which does not provide rank or partition by)
Thanks in advance,
Tamas
I'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]
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???
A 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.
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
Hello,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?
Dear 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
Hi, 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
Hi 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.
hello 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; }
How 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.
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.
I 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.
CREATE 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?
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.
Hi -- 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.
We 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).
I 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
Can 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?
I 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...
Hello . 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 .
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?
I 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?
This 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.
Hello, 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.
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".
Hi 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)
I 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
Okay, 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
I 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:
