Hi there - 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

Hi there

Author

Topic

ayossib
Starting Member

3 Posts

Posted - 2013-06-27 : 09:20:55

I have a little confusion with something.
what is the difference between these 2 sql queries:

(!!!my goal was to get the min id and the first name!!!)

SELECT id, firstName
FROM employee
WHERE id = (SELECT MIN(id) FROM employee)

or

SELECT min(id), firstName
FROM employee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 09:45:17

first one gives you record(s) with min id value from the table employee

second one is not syntactically correct

perhaps you meant


SELECT min(id), firstName
FROM employee
group by firstname

gives you minimum id value for each employee

which gives

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ayossib
Starting Member

3 Posts

Posted - 2013-06-27 : 09:55:21

why the second one is not syntactically correct?

The SQL MIN() Syntax is not:
SELECT MIN(column_name) FROM table_name

??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-27 : 10:30:44

quote:
Originally posted by ayossib

why the second one is not syntactically correct?

The SQL MIN() Syntax is not:
SELECT MIN(column_name) FROM table_name

??

That is only part of the syntax. The other rule is that any column in your select list that is outside of an aggregate function (such as MIN in your example), must also be listed in the group by clause. So both of the following are correct:

SELECT MIN(column_name) FROM table_name;
SELECT MIN(column_name), another_column FROM table_name group by another_column

But the following is NOT correct:

SELECT MIN(column_name), another_column FROM table_name

ayossib
Starting Member

3 Posts

Posted - 2013-06-27 : 10:35:32

Now it's clear.
Thanks you all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 10:37:11

quote:
Originally posted by ayossib

why the second one is not syntactically correct?

The SQL MIN() Syntax is not:
SELECT MIN(column_name) FROM table_name

??

sql min syntax is not wrong

but your second statement was

SELECT min(id), firstName
FROM employee

you cant simultaneously select a column and apply aggregate over other unless you group on the individual column

why not try compiling both?


SELECT min(id), firstName
FROM employee

will give this error


column firstName is invalid in select clause because its not contained in a aggregation or a GROUP BY clause

whereas this works

SELECT min(id), firstName
FROM employee
group by firstname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources