Distinct Selection in SELECT

Hi all, How can I achieve this?
select col1,distinct(col2),col3,col4 from table1
TIAI have no idea what you are trying to achieve.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Hi all, How can I achieve this?
> select col1,distinct(col2),col3,col4 from table1
>
> TIA
>|||--Record present
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
john cvse 12/31/2002
SELECT Col1,Distinct(Col2),COl3 From Table1
-- desired recordset
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> I have no idea what you are trying to achieve.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
>|||Can you explain why jerry and not john? And if it were like this:
mark 1qa 20030505
melody 1qa 20020608
jerry cvse 20021231
john cvse 20030104
What would the output be then?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
> --Record present
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
> john cvse 12/31/2002
> SELECT Col1,Distinct(Col2),COl3 From Table1
> -- desired recordset
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
>|||I guess sql does a max like top..or something..I am not sure
though that's what I am trying to achieve I want distinct on col2 yet want
to select the other cols too...how can I accomplish it?
TIA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uIhTtllLFHA.700@.TK2MSFTNGP10.phx.gbl...
> Can you explain why jerry and not john? And if it were like this:
> mark 1qa 20030505
> melody 1qa 20020608
> jerry cvse 20021231
> john cvse 20030104
> What would the output be then?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
>|||> I guess sql does a max like top..or something..I am not sure
> though that's what I am trying to achieve I want distinct on col2 yet
want
> to select the other cols too...how can I accomplish it?
You still haven't defined what you want to accomplish. You want one row for
every col2. However, if there are multiple different values for col1 and
col3, you need to define how SQL Server will determine which ones to
include.
If you do not care, say so. "ANY col1 / col3 will do." Maybe one of these
is what your after, though I question the usefulness of the extra columns if
this is the case:
SELECT MAX(Col1), Col2, MAX(col3)
FROM Table1
GROUP BY Col2
(Notice that Col1 will not necessarily come from the same row as Col3, which
is why I asked about the date AND the name that you wanted returned.)
If you do care, say so. Provide REAL specs (not typing out your data in
tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
full, including the link to generate insert statements) and we can provide a
real solution.
A|||great! works for me
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uhGB69lLFHA.3788@.tk2msftngp13.phx.gbl...
> want
> You still haven't defined what you want to accomplish. You want one row
for
> every col2. However, if there are multiple different values for col1 and
> col3, you need to define how SQL Server will determine which ones to
> include.
> If you do not care, say so. "ANY col1 / col3 will do." Maybe one of
these
> is what your after, though I question the usefulness of the extra columns
if
> this is the case:
> SELECT MAX(Col1), Col2, MAX(col3)
> FROM Table1
> GROUP BY Col2
> (Notice that Col1 will not necessarily come from the same row as Col3,
which
> is why I asked about the date AND the name that you wanted returned.)
> If you do care, say so. Provide REAL specs (not typing out your data in
> tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
> full, including the link to generate insert statements) and we can provide
a
> real solution.
> A
>sql

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.

Display ONLY Month, year from SQL2k

Tried this:

SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM ....

That returns: Apr 01, 2006
What I need is this: April 2006 or even Apr 2006. But no date for the day.

Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!

bs.

SELECT Left(CONVERT(varchar,getdate(),107), 4) + RIGHT(CONVERT(varchar,getdate(),107), 4) AS 'Month in Question'|||

Thank you.

while you gave an acceptable answer, I found another way - I'll still mark your answer as right because it answered my question originally.

This is what I used to get it to work:

SELECT CONVERT(VARCHAR(10), DATENAME(MM,fieldMonthYear)) + ', ' + CONVERT(VARCHAR(4), Year(fieldMonthYear)) AS 'Month in Question' FROM tblTableName

God bless...
iSheahan

|||

There is a better way to format datetime from client side.

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource3">
<ItemTemplate>

LastActivityDate:
<asp:Label ID="DateLabel" runat="server" Text=' <%# DataBinder.Eval(Container.DataItem, "fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

</ItemTemplate>

</asp:DataList>

Or simply: <asp:Label ID="Label1" runat="server" Text=' <%# Eval("fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

Enjoy.