SQL select statement error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL select statement error

Author

Topic

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-26 : 09:39:22

hello

so i have the following:
a database called movies with: id-int, primary key, name varchar and priority int.
I have inserted the following records:
1 The Bucket List 9
2 The Wrestler 8
3 LOTR The Fellowship of the Ring 10
4 LOTR The Two Towers 10
5 LOTR The Return of the King 10

when i excute the command:

select name from movies where priority=(select max(priority) from movies)

it returns the result: The Bucket List

and when i execute the command:

select name from movies where priority=(select min(priority) from movies)

it returns: LOTR The Fellowship of the Ring
LOTR The Two Towes
LOTR The Return of the King
Why? What did i do wrong?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-26 : 09:48:56

[code]
(select max(priority) from movies)
[/code]
this will gives you the maximum priority. From the sample data, the max is 10, and there are 3 records with max 10

so your query "select name from movies where priority=(select max(priority) from movies)" Will return 3 records.

What is the required result ?

Check out row_number() in Books OnLine. This function will be able to gives you what you want

KH
[spoiler]Time is always against us[/spoiler]

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-26 : 10:02:19

The required result for the query you wrote above is : LOTR 1, LOTR2, LOTR 3. but it doest return that, it returns The Bucket List as i said. And for the same query but with min instead of max it returns LOTR1,LOTR2,LOTR3, whereas it should return The Wrestler

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-26 : 10:12:19

The only explanation I can think of is that the data type of priority column is not INT as you have indicated. It may be something like VARCHAR or NVARCHAR. You can test that theory easily by trying this to see if it gives the expected result:

select name from movies where priority=(select max(CAST(priority AS INT)) from movies)

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-26 : 10:17:48

Oh...my bad... You're right. i got cought up with everything and forgot what type i assigned that column with...Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 10:17:52

Can you double check your table design? When I tested I get exactly what you said it should return.


CREATE TABLE Movies (
id int, 
name VARCHAR(50),
priority INT
)

INSERT INTO movies VALUES 
(1, 'The Bucket List', 9),
(2, 'The Wrestler', 8),
(3, 'LOTR The Fellowship of the Ring', 10),
(4, 'LOTR The Two Towers', 10),
(5, 'LOTR The Return of the King', 10)

select name from movies where priority=(select max(priority) from movies)
/*
returns
LOTR The Fellowship of the Ring
LOTR The Two Towers
LOTR The Return of the King
*/

select name from movies where priority=(select min(priority) from movies)
/*
returns
The Wrestler
*/

Check that the priority column really is an int, not a string. If it's a string then 10 will be minimum and 9 maximum, so the min will return the 3 LotR movies and max will return The Bucket List. That's because in string comparison 9 is greater than 1.

--
Gail Shaw
SQL Server MVP

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-27 : 05:04:32

Yes if the datatype is varchar then it will display as in different format...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources