Simple syntax question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple syntax question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-21 : 17:20:25

Hi,

Whats the correct statement to do this

SELECT DISTINCT ModText as x, (SELECT ManText From Vehicles WHERE ModText = x)
FROM Vehicles

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 17:35:26

Could you show us a data example?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-21 : 17:39:01

Is the subquery going to return multiple values?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-21 : 17:42:03

Okay simple example, table containing makes, models

AUDI A3
AUDI A4
BMW 318
BMW 320

etc etc

I want all the distinct models and also the corresponding makes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 17:45:29

And why doesn't this return what you want:

SELECT Make, Model
From Vehicles

Could you show us what the data looks like in the table? I am assuming that your previous reply is the expected output.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-06-21 : 17:53:54

Hi, thanks for replies.

Each make/model appears hundreds of times, I only want the distict models (and the make that goes with it)

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 17:54:59

SELECT DISTINCT Make, Model
FROM Vehicles

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-21 : 18:08:16

SELECT Make, Model
From Vehicles
GROUP BY Make, Model

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 19:44:59

It has never made sense to me to use GROUP BY without some aggregate function. DISTINCT does this already for us, so I prefer to type less characters.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-21 : 23:47:14

I think you want distinct make and any random model right?

Select make, max(model)
from tablename
group by make

--------------------------------------------------
S.Ahamed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-22 : 00:49:08

Not according to Mondeo's second post. The expected result set shows multiple models.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources