DISTINCT

Hi,
I need to only pull distinct values from my database ie...
SELECT DISTINCT Type, ClickID, Email, FullApp
FROM tblApps
However I also want to get other fields that also are not distinct ie the
record ID number, but if I include the ID number then I get all the rows.
How can I apply DISTINCT on just a few fields, but still return every field
in the table?
--
Regards
Gary Howlett
Systems Developer
www.rainbowgrp.co.ukHi Gary,
The question you have to ask yourself is, when you return the distinct
values from some columns, and also columns with values that are not
distinct, how do you determine which values you are going to return? If you
have a 2 rows with the same Type, ClickID, Email and FullApp, the ID of
which row do you want to return? The highest ID, the lowest ID, a random ID?
If you want the highest or the lowest you can use MAX() or MIN(), a random
one is a bit more difficult.
hth
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||Maybe what you're looking for is to use the GROUP BY clause. If I
understood your question, you're looking to group by a few of the fields,
and still get the other fields. Since you're grouping by some of the
fields, the other fields will have to be returned in some sort of aggregate
function.
An example would be this (run in Query Analyzer):
use northwind
select CustomerID, min(OrderDate) FirstOrderDate
from Orders
group by CustomerID
You essentially get all the "distinct" CustomerIDs, but of course any other
fields would have to be aggregated (see the BOL for the other aggregate
operations available). Every non-grouped field will have to be aggregated
in some way.
HTH
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||You can't expect to select distinct and select the
record_id.
The record_id is unique, therefore, distinct.
You need to understand exactly what you want to retrieve
with the query.
Regards
>--Original Message--
>Hi,
>I need to only pull distinct values from my database ie...
>SELECT DISTINCT Type, ClickID, Email, FullApp
>FROM tblApps
>However I also want to get other fields that also are not
distinct ie the
>record ID number, but if I include the ID number then I
get all the rows.
>How can I apply DISTINCT on just a few fields, but still
return every field
>in the table?
>--
>Regards
>Gary Howlett
>Systems Developer
>www.rainbowgrp.co.uk
>
>.
>