Distinct applies to all fields instead of one 1

[mfranck]

When I write a statement like

select distinct titel, type from books

And I have two entries like

titel type
Nice book IT-book
Nice book non IT book

I'll get both entries.
How can I write a querry that applies the distinct to the title column only?
I tried select

(distinct title), type from books

but only got an error message.
Can anybody help? Thank you

Mike

 

[SQLSister]

What results are you expecting?

 

[checkai]

Great Question !! This could give you the top 1...hmmm what are you looking foR???

select distinct b.title,
(select top 1 type from books a where a.title=b.title)

from books b

dlc

 

[SimonSellick]

If you specify DISTINCT your output columns must include only those that you want to see distinct values for - otherwise, what is the query processor meant to return for the other columns you specify? It's like GROUP BY, but without the facility to use aggregate functions on the 'spare' (non-distinct) columns.

 

[mfranck]

Hmm, I see now why this question might be unprecise. What I was looking for was apparently a simple

select titel, typ from books group by titel

This works great with MySQL. Unfortunately this statement has to work on MSSQL which returns an error that I nee to use typ in the group by because it is part of the select.

Any idea how to get around this?

 

[r937]

yes, you can get around it by using an aggregate function on all columns in the SELECT list that are not in the GROUP BY

e.g. arbitrarily choosing the lowest typ for each titel

[tt]select titel
, min(typ)
from books
group
by titel[/tt]

and FYI, it does not work "great" in mysql

mysql's group by processing is egregiously non-standard

but to give them credit, at least they warn you about it --

see 6.3.7.3 GROUP BY with Hidden Fields
(

http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html)

Don't use this feature if the columns
you omit from the GROUP BY part aren't unique
in the group! You will get unpredictable results.

that's mysql's emphasis, not mine

in your example, typ is not unique within titel




rudy

http://r937.com/

SQL Consulting