DISTINCT ORDER BY PROBLEM

I have q query like this
SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
'Kolye') ORDER BY urunid
it returns
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
i tried
SELECT DISTINCT ([cinsiyet]),urunid FROM URUNLER
WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
'Kolye') ORDER BY urunid
it returns
cinsiyet urunid
Erkek 1945
Unisex 1946
Unisex 1947
Erkek 1948
Unisex 1949
Erkek 1950
Erkek 1951
Bayan 1952
but i want cinsiyet field to be distincted and ordered by urunid
cinsiyet urunid
Erkek 1945
Unisex 1946
Bayan 1952
What query i should use to get it ?Savas Ates wrote:

> SELECT DISTINCT ([cinsiyet]),urunid FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Unisex 1947
> Erkek 1948
> Unisex 1949
> Erkek 1950
> Erkek 1951
> Bayan 1952
>
> but i want cinsiyet field to be distincted and ordered by urunid
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Bayan 1952
SELECT cinsiyet,min(urunid)
FROM URUNLER
WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
'Kolye')
GROUP BY cinsiyet
ORDER BY urunid
Dieter|||Savas wrote on Wed, 14 Dec 2005 15:08:00 +0200:

> I have q query like this
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> i tried
> SELECT DISTINCT ([cinsiyet]),urunid FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Unisex 1947
> Erkek 1948
> Unisex 1949
> Erkek 1950
> Erkek 1951
> Bayan 1952
> but i want cinsiyet field to be distincted and ordered by urunid
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Bayan 1952
> What query i should use to get it ?
>
DISTINCT works on an entire row, not an individual column. ORDER BY doesn't
work because you're not selecting it. Try this:
SELECT [cinsiyet] FROM URUNLER
WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
'Kolye') GROUP BY [cinsiyet] ORDER BY MIN(urunid)
This will sort the results by the smallest urunid, if you want to use the
largest use MAX instead.
Dan|||Server: Msg 8127, Level 16, State 1, Line 1
Column name 'URUNLER.urunid' is invalid in the ORDER BY clause because it is
not contained in either an aggregate function or the GROUP BY clause.
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:OX4hv9KAGHA.2812@.TK2MSFTNGP09.phx.gbl...
>I have q query like this
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> i tried
> SELECT DISTINCT ([cinsiyet]),urunid FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Unisex 1947
> Erkek 1948
> Unisex 1949
> Erkek 1950
> Erkek 1951
> Bayan 1952
>
> but i want cinsiyet field to be distincted and ordered by urunid
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Bayan 1952
> What query i should use to get it ?
>|||Savas wrote on Wed, 14 Dec 2005 15:17:08 +0200:

> Server: Msg 8127, Level 16, State 1, Line 1
> Column name 'URUNLER.urunid' is invalid in the ORDER BY clause because it
> is not contained in either an aggregate function or the GROUP BY clause.
I'm assuming that is in reply to Dieter's post. Did you look at my solution?
Dan|||YEp
it was for Dieter's post.. Thank you guys both. Yours is working Dan ;)
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:OX4hv9KAGHA.2812@.TK2MSFTNGP09.phx.gbl...
>I have q query like this
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> i tried
> SELECT DISTINCT ([cinsiyet]),urunid FROM URUNLER
> WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik' ,
> 'Kolye') ORDER BY urunid
> it returns
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Unisex 1947
> Erkek 1948
> Unisex 1949
> Erkek 1950
> Erkek 1951
> Bayan 1952
>
> but i want cinsiyet field to be distincted and ordered by urunid
> cinsiyet urunid
> Erkek 1945
> Unisex 1946
> Bayan 1952
> What query i should use to get it ?
>|||Savas wrote on Wed, 14 Dec 2005 15:25:19 +0200:

> YEp
> it was for Dieter's post.. Thank you guys both. Yours is working Dan ;)
Just a quick tip - it helps to reply to the post you're replying to so that
threads are easy to follow, rather than posting replies to your own original
post ;)
Dan|||What about this:
SELECT cinsiyet, MAX(urunid)
FROM URUNLER
WHERE cinsiyet NOT IN ('Bileklik' , 'Yuzuk' ,'Set' ,'Kupe' ,'Bilezik'
, 'Kolye')
GROUP BY cinsiyet
ORDER BY MAX(urunid)
HTH, jens Suessmeyer.