Please solve this sql query

Question

Please solve this sql query

anshu84onnet

SSC Eights!

Points: 836
More actions
January 26, 2010 at 9:01 am

#144551

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

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

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.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

anshu84onnet SSC Eights! Points: 836 More actions · Answer 4

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...

Shriji SSCommitted Points: 1881 More actions · Answer 5

Shriji

SSCommitted

Points: 1881

January 27, 2010 at 3:26 am

#1109191

Again, what do you expect from the query?

anshu84onnet SSC Eights! Points: 836 More actions · Answer 6

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...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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?

LutzM SSC Guru Points: 107049 More actions · Answer 8

@ Lynn:

I'd experct the second query to be SELECT * instead of another SELECT 8...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

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?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

LutzM SSC Guru Points: 107049 More actions · Answer 13

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...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Shriji SSCommitted Points: 1881 More actions · Answer 14

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?