I¡¦ve got a table with the following as well as other info:
User ID
DirectoryTypeID (int)
Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Hello, I have a question on sql stored procedures. I have such a procedure, which returnes me rows with ID-s. Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? (without sending it to page) Here is it:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById] ( @Id int ) AS declare @path varchar(100); SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id; SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories] WHERE Path LIKE @Path + '%' ORDER BY Path
fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.
I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
Hi all, I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file. How can i do that. I am using asp.net vb.net.
In a query in Management Studio, I want to output data to a text file (via Results in text button) without column names and hyphens or dashes, I have searched all SET commands with bad luck because SET FMTONLY OFF do exactly the opposite.
I know this could be a silly question because I have received suggestions in other ways to do that, but I am intrigued If there is a way to prevent column name from being displayed/returned from a query.
@@version yields
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer. Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16). Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned. If someone is able to help me figure this out, I would appreciate it. 1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList); 13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
G'day, I have a datetime column that holds dates and times like 30/06/2005 1:31:00 PM How would I find all rows that match a certain date regardless of the time, (IE, select all rows with a date of 30/06/2005 with any time)
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
date item_id grade in out ------ ------- ------- ----- ----- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total ------- --- ---- --- --- ------- 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 -8 20 -10 2
I want result group by Item_id and sum of grade in column . where grade could be any Alphabet, the column of result query could varies. Each grade contains sum of (in-out) of item_id of detail table.
HiYour help for the following query will be highly apprecaited. I'vewasted alot of time on it. Data definition is at the bottom.Thanks-----------------------------------Business need: User selects answers for few questions. Answers arepresented in the form of radio buttons.Questions Table:===================QuestionID QuestionName1 1.Rate your organization2 10.Opportunity in your group3 117.Effectiveness----------------------------------------Answers Table:==============AnswerId AnswerName QuestionID1 1.Best 12 2.Average 13 3.Wrose 14 1.Hardly Any 25 2.not too much 26 3.Average 27 4.Great Deal 28 1.Strong 39 2.Minor 310 3.Nothing 3--------------------------------------------------------Lets say User ABC answered below:For Question 1, she chose '2.Average'For Question 2: she left blankFOr Quesiton 3: she chose '2.Minor'And for user XYZ:For Question 1, she let blankFor Question 2: she left blankFOr Quesiton 3: she chose '3.Nothing'Then the Results table will look like this:Results Table:===============Resultsid QuestionID AnswerId User1 1 2 ABC2 3 9 ABC3 3 10 XYZ---------------------------------------------------Desired Output:===============User 1 10 117--- -- --- ----ABC 2 2XYZ 3Explanation:--> Show ALL distinct users on rows--> show ALL distinct QuestionName on the columns BUT only display thenumber before '.'--> Show the chosen answer (answername) for each user but only displaythe number before '.'-------------------------------------------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[Questions]GOCREATE TABLE [dbo].[Questions] ([QuestionId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionName] [nvarchar] (1000) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO-------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Answers]GOCREATE TABLE [dbo].[Answers] ([AnswerId] [int] IDENTITY (1, 1) NOT NULL ,[AnswerName] [nvarchar] (150) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[QuestionId] [int] NULL) ON [PRIMARY]GO----------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Results]GOCREATE TABLE [dbo].[Results] ([ResultId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionId] [int] NULL ,[AnswerId] [int] NULL ,[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GO---------------------------------------------INSERT Answers (AnswerName ,QuestionId)VALUES ('1.Best' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Average' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Wrose' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Hardly Any' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.not too much' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Average' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('4.Great Deal' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Strong' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Minor' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Nothing' ,3)go-----------------------------INSERT Questions (QuestionName)VALUES ('1.Rate your organization')goINSERT Questions (QuestionName)VALUES ('10.Opportunity in your group')goINSERT Questions (QuestionName)VALUES ('117.Effectiveness')go------------------------------------INSERT Results (QuestionId ,AnswerId ,UserId)VALUES (1 ,2 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,9 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,10 ,'XYZ')go
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date. Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned. BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times. Can anybody help resolve this?
SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,
[DbName].[SchemaName].[TableName2] AS t2
WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)
AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
I have a qry/dataset in a report. If the qry returns no data the report will simply show a the headers/footers and no data in the detail section. However, I need to display all zeros if the detail section ie '0', if no data is returned. How can I do that? I have SSRS 2005.
I get 100 rows of result, i want to write a sql query similar to the one available in MySql database where in i can specify the starting row and number of rows of records i want,
something like,
select * from emp LIMIT 10,20
I want all the records from the 10th row to the 20th row.
This would be helpful for me to do paging in the front end , where is i can navigate to the next previous buttons and fetch the corresponding records.
I want to do something like in google, previous next screens.
So I am trying to limit the number of rows fetched from a query.
somethin like,
select * from emp where startRowNum=10 and NoOfRecords = 20
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Hello and thank you taking a moment. I have created a simple login page where the user will pass credentials. I have a sqldatasource that will query a database to see if the user exists. What I would like to do is have the user click a button after entering their credentials. When the button is clicked I would like the SqlDatasource SelectCommand to fire and if there are rows returned then redirect the user to a new page. I know I can do this with ADO and a datareader with the HasRows property. But what I would eventually like to do is cache the data and then bind the cached data to a control(like a dataview) on the page the user is redirected to. If anyone can tell me how to get the Select command to fire on a button click I would be eternally grateful. Any help will be greatly appreciated.
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
