| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-01-10, 13:59
|
|
Registered User
|
|
Join Date: Jan 2009
Posts: 6
|
|
Concept of this SQL query
|
|
My SQL query is as follows
Code:
select title
from book as B
where (select count(*)
from book as T
where (T.price > B.price) < 5
This query returns the 5 most expensive books. If I do >5, it is returning the 4 most expensive books. What is the concept behind this? Why is it so?
|
|
02-01-10, 14:25
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,511
|
|
Quote:
Originally Posted by mahendrakariya
If I do >5, it is returning the 4 most expensive books.
|
no, it does not
Code:
CREATE TABLE book
( title VARCHAR(99)
, price INTEGER
);
INSERT INTO book VALUES
( 'book 1' , 40 )
,( 'book two' , 50 )
,( 'three' , 30 )
,( 'book 5' , 10 )
,( 'six ' , 30 )
,( '7even' , 40 )
,( 'eight' , 15 )
,( 'book 9' , 25 )
,( 'book 10' , 35 )
SELECT price,title FROM book ORDER BY price DESC;
price title
50 book two
40 book 1
40 7even
35 book 10
30 three
30 six
25 book 9
15 eight
10 book 5
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) < 5
order by price desc
price title
50 book two
40 book 1
40 7even
35 book 10
30 three
30 six
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) > 5
order by price desc
price title
25 book 9
15 eight
10 book 5
|
|
02-01-10, 23:54
|
|
Registered User
|
|
Join Date: Jan 2009
Posts: 6
|
|
|
|
That is because some of the books have the same price. If we have different price for each book, the result is something different.
Code:
CREATE TABLE book
( title VARCHAR(99)
, price INTEGER
);
INSERT INTO book VALUES
( 'book 1' , 40 )
,( 'book two' , 50 )
,( 'three' , 60 )
,( 'book 5' , 70 )
,( 'six ' , 80 )
,( '7even' , 90 )
,( 'eight' , 105 )
,( 'book 9' , 115 )
,( 'book 10' , 125 )
SELECT price,title FROM book ORDER BY price DESC;
price title
125 book 10
115 book 9
105 eight
90 7even
80 six
70 book 5
60 three
50 book two
40 book1
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) < 5
order by price desc
price title
125 book 10
115 book 9
105 eight
90 7even
80 six
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) > 5
order by price desc
price title
60 three
50 book two
40 book1
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) < 3
order by price desc
price title
125 book 10
115 book 9
105 eight
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) > 3
order by price desc
price title
80 six
70 book 5
60 three
50 book two
40 book1
|
|
02-02-10, 00:00
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,511
|
|
Quote:
Originally Posted by mahendrakariya
select price,title
from book as B
where ( select count(*)
from book as T
where T.price > B.price ) > 5
order by price desc
price title
60 three
50 book two
40 book1
|
you have proved here, yourself, that your first post was incorrect
in your first post, you said "If I do >5, it is returning the 4 most expensive books."
clearly, 60-50-40 is ~not~ the 4 most expensive
see what i mean?
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Concept of this SQL query
