Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
>
>
>
> But this won't give you any particular row. It will be a "frankenstein" r
ow
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-
