January 26, 2010 at 9:01 am
i am writing this query--' Select 8 from table' and this is returning number of rows present in table times 8 as output please tell me the reason why we get this output please test this query in your database for any table it will return number of rows present in that table times as 8
i want the perfect reason in brief
thanks
January 26, 2010 at 9:10 am
What are you wanting to return? Or expecting the query to return? The query is actually performing EXACTLY what you asked. No matter the number of rows in the table, the query will place the number 8 in each of the rows, instead of the data from the table. That is the syntax that you have, and that is what the query is returning.
Now do you want to see only the TOP 8 rows? If so, then SELECT TOP 8 * from <TableName>
If not, I suggest that you read the SQL Books Online for more information about how to write the query that you need.
Andrew SQLDBA
January 26, 2010 at 9:11 am
What did you expect the query to return?
First of all, you to do not have a WHERE clause to restrict the number rows that the query will be run against so the query will return one one record for each row in the database.
Second, you're saying SELECT 8 FROM .... this will return the number 8
Combine these two together and you will get the number 8 as many times as there are rows in the table.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 26, 2010 at 9:21 am
I'm thinking you meant to type this:
select * from tablename
Looks to me like you forgot to hold the shift key when you press the 8.
January 27, 2010 at 3:19 am
thanks for your reply dear i have not make any mistake in pressing the shift key it will return number of rows present on that table what so ever number you enter i want to know y the output is like that is it due to compilation of sql server or some properties plz reply urgent...
January 27, 2010 at 3:26 am
Again, what do you expect from the query?
January 27, 2010 at 4:08 am
how can i explain it to you dear just want to know that mechanism behind execution of that query why any number is returned number of times rows...
January 27, 2010 at 6:15 am
First, don't call me dear. It isn't appropriate.
Second, you are getting just what you are asking for in your select.
SELECT -- Have SQL return something to me
8 -- In this case, the constant value 8
FROM
dbo.MyTable -- from MyTable
SELECT -- Have SQL return something to me
* -- In this case, all columns
FROM
dbo.MyTable -- from MyTable
In the first, you get 8 returned in as many rows exist in dbo.MyTable. In the second, you get all the data in all the rows in dbo.MyTable.
Substitute any table in your database for dbo.MyTable in the above queries and you will see that is what occurs.
Does this help your understanding?
January 27, 2010 at 6:25 am
@ Lynn:
I'd experct the second query to be SELECT * instead of another SELECT 8...
January 27, 2010 at 6:58 am
lmu92 (1/27/2010)
@ Lynn:I'd experct the second query to be SELECT * instead of another SELECT 8...
Thank you. Lutz. That was a case of not holding the shift key as i retyped and I didn't catch it as i was in a hurry to get out the door to work.
Fixed the code in the previous post.
January 27, 2010 at 7:03 am
anshu84onnet (1/27/2010)
how can i explain it to you dear just want to know that mechanism behind execution of that query why any number is returned number of times rows...
See my answer, right after your question. What else do you want?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 27, 2010 at 7:11 am
Also, if the explanations you have gotten here aren't enough, or you'd like more information, I highly suggest that you read about the SELECT statement in BOL (Books Online, the SQL Server Help system).
January 27, 2010 at 7:15 am
anshu84onnet (1/27/2010)
how can i explain it to you dear just want to know that mechanism behind execution of that query why any number is returned number of times rows...
Try this:
SELECT 'WTF', * FROM dbo.MyTable
SELECT 'WTF' FROM dbo.MyTable
Obvious now innit?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 27, 2010 at 9:26 am
Going back to your very first post:
anshu84onnet (1/26/2010)
i am writing this query--' Select 8 from table' and this is returning number of rows present in table times 8 as output
I tested the query and couldn't confirm what you describe. I get exactly what has been described in previous posts by others.
In order to understand what you're trying to do I'd like to ask you to provide some test data in a ready to use format that actually support your statement from above.
Something like the following:
DECLARE @t TABLE (Val VARCHAR(10))
INSERT INTO @t
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd' UNION ALL
SELECT 'e' UNION ALL
SELECT 'f'
SELECT * FROM @t
SELECT 8 FROM @t
If you try this sample code you'll notice that both queries will return 6 rows.
If your original statement would be true, than the 2nd query would have returned 48 rows (6*8). But it didn't.
Either your statement is incorrect or I'm using the wrong sample data...
January 27, 2010 at 1:44 pm
Okay I ran following query against table1 which has 10 rows:
select 8 from table1
It returns a single column result with 10 rows with each row having value 8 in it. As simple as that.
Are you running this query as an in-line query (sub query) of any other query?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply