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...
I looked around and couldn't find anything on it.
thanks in advance
HiBeen at this for 2 days now.Each business has several packages which they can sort usingsort_order.I'm trying to get one package for each business(that I can do), howeverI want it to be the one with the lowest sort_order valueAs you can see below the first record has sort_order=5 when it shouldbe 1.Most of the sort_order columns will be zero by defaultAny help so i can get on with my life!CheersGary------------Current select-------------------SELECT *FROM dbo.testAccommodation_Packages T1WHERE (NOT EXISTS(SELECT *FROM testAccommodation_PackagesWHERE business_id = T1.business_id AND Package_ID < T1.Package_ID))--------------results:-----------------------Package_IDbusiness_iditem_namesort_order123rd Night FREE ...5113Donegal Town ... 0204Executive ...0--------------To recreate----------------------CREATE TABLE [testAccommodation_Packages] ([Package_ID] [int] IDENTITY (1, 1) NOT NULL ,[business_id] [int] NULL ,[Item_Name] [nvarchar] (300) NOT NULL ,[sort_order] [int] NULL CONSTRAINT[DF_Accommodation_Packages_sort_order] DEFAULT (0),)-------------------------------------------------INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 EuroPPS)','5')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1Dinner Only € 129 PPS','4')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149Euro PPS','3')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D€169pps','2')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5course Lunch 159 Euros PPS (~109 Stg)','1')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('6','2','April Pamper Package - 2 Night Special ONLY€195pps','10')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119PPS','7')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner€229pps','8')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner€169pps','9')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55Stg) Double Room','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner109.00euros pps','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four coursegourmet dinner 99Euro PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B€129.00p.p.s.','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €139 PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('21','4','Watercolour Painting Break 3 B&B Full Board andTuition € 335 PPS','0')
View 1 Replies View RelatedI want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!
View 1 Replies View RelatedHi,
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Many thanks
Rob Gibson
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]
[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...
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
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.
Can anyone explain why?
Thanks
-Jamie
Hi,
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
Hi,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 RelatedHello,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 RelatedHi Folks,
You can select all fields with:SELECT * FROM [TableName]
How do you select NOTHING or NONE with a SELECT statement?
Thanks
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
View 1 Replies View RelatedHi 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 Relatedhello 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.
View 1 Replies View RelatedHello:
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?
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?
View 1 Replies View RelatedHi
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
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.
Thank-you for your help.
how can I select 10 random rows from a table?
Please help
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
View 2 Replies View RelatedCan 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.
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 .
What is the problem then ?
Hello,
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
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?
View 2 Replies View RelatedThis 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
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.
Any help would be appreicated.
-Matt
Hi,
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
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
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