Distinct

I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as

Select *
from Table
Where Distinct ColumnName

I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this

select distinct columnName,columnName2
from table

it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name

thanks for any helpSo you are saying that you have for example

Column1, Column2
1 2
1 3
1 4
1 5

Or is it that you have

1 1
1 1
1 2

etc ?

If your scenario is the first case then you essentially want 1,5 I assume ? and if it is the second case you want 1, 1 followedb by 1, 2 ?

Here is one article that is pretty good about describing ways it is on page three of three that shows you possible code.

http://www.databasejournal.com/features/mssql/article.php/10894_2235081_3|||the first example would be the one that i have but if this was the data
1,1
1,2
1,3
1,4
2,1
2,2
2,3
3,1
3,2

then what i would like the query to return is the first of the set only so...
1,1
2,1
3,1|||You can use a group by clause with th MIN aggregate function to get the results

SELECT ColumnA, MIN(ColumnB)
FROM Table1
GROUP BY CoulmnA

If you wanted the results to be {1,4}, {2,3}, {3,2} you would use the MAX function instead.

Navin|||You can use a group by clause with th MIN aggregate function to get the results

SELECT ColumnA, MIN(ColumnB)
FROM Table1
GROUP BY CoulmnA

Navin|||i am working with this and it works nicely but it mixes up records i have about 5 different rows and it only gives me one record but it is jumbled information
example

col1,col2,col3,col4,col5
jon,bob,123,999,MI
james,duke,321,444,OR

and my results are
jon,duke,123,444,MI

here is my sql command

SELECT t_Location.LocationID, MIN(t_Followup.FollowupDate) AS FollowupDate, MIN(t_Provider.ProviderLName) AS ProviderLName,
 MIN(t_Provider.ProviderFName) AS ProviderFName, MIN(tlkup_FollowupType.FollowupType) AS FollowupType,
 MIN(tlkup_ActivityType.ActivityTypeName) AS ActivityTypeName, MIN(t_Activity.ActivityDate) AS ActivityDate, MIN(t_Provider.ProviderID) AS ProviderID,
 MIN(t_Followup.FollowupID) AS FollowupID
FROM tlkup_FollowupType INNER JOIN
 t_Followup ON tlkup_FollowupType.FollowupTypeID = t_Followup.FollowupTypeID INNER JOIN
 t_Activity ON t_Followup.ActivityID = t_Activity.ActivityID INNER JOIN
 tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN
 t_Location ON t_Followup.LocationID = t_Location.LocationID AND t_Activity.LocationID = t_Location.LocationID INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID
WHERE (t_Followup.RepID = @.RepID) AND (t_Followup.FollowupDate <= @.FollowupDate) AND (t_Followup.RespondedTo = 0)
GROUP BY t_Location.LocationID
ORDER BY MIN(t_Followup.FollowupDate)

is there something i am missing
looking at it now looks like the problem might be in the orderby command with the min statement but it would not work at all because it said i needed something so i put in the min statement|||i took out the order by statement and it still had the same problem
so i have no idea what could be happening|||Only use the MIN keyword on the column for which you want the MIN value.|||alright i did it on this

SELECT t_Location.LocationID, t_Followup.FollowupDate, t_Provider.ProviderLName, t_Provider.ProviderFName, tlkup_FollowupType.FollowupType,
 tlkup_ActivityType.ActivityTypeName, t_Activity.ActivityDate, MIN(DISTINCT t_Provider.ProviderID) AS ProviderID, t_Followup.FollowupID
FROM tlkup_FollowupType INNER JOIN
 t_Followup ON tlkup_FollowupType.FollowupTypeID = t_Followup.FollowupTypeID INNER JOIN
 t_Activity ON t_Followup.ActivityID = t_Activity.ActivityID INNER JOIN
 tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN
 t_Location ON t_Followup.LocationID = t_Location.LocationID AND t_Activity.LocationID = t_Location.LocationID INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID
WHERE (t_Followup.RepID = 13) AND (t_Followup.FollowupDate <= '5/5/04') AND (t_Followup.RespondedTo = 0)
GROUP BY t_Followup.FollowupID, t_Provider.ProviderLName, t_Provider.ProviderFName, t_Location.LocationID, t_Followup.FollowupDate,
 tlkup_FollowupType.FollowupType, tlkup_ActivityType.ActivityTypeName, t_Activity.ActivityDate, t_Followup.FollowupID

and then it showed all the results availiable and not just the one i want
is there something i am missing with the group by function
because it says if there is no MIN or MAX function on the column then it needs the group by

Thanks for your quick response

I would like it to display only one provider along with the location information and followup info
everything is relying solely on the location table|||Don't use DISTINCT and MIN at the same time; remove the DISTINCT keyword...|||i removed the distinct and it still displayed all of the results
is there anyway that i can use this command without using the groupby function for everything
is there another command that says something like Go with the flow
:)
All i would like it to do is to display one person for each locationid
but it is displaying everyone at this location

(i may have said that before but i am not sure)|||This is how I've solved this sort of problem in the past:

SELECT
 T1.ColumnA,
 T1.ColumnB,
 T1.ColumnC,
 T1.ColumnD
FROM
 Table1 T1
INNER JOIN
(
SELECT
 ColumnA,
 MIN(ColumnB) AS ColumnB
FROM
 Table1
GROUP BY
 ColumnA
) AS SubQuery ON SubQuery.ColumnA = T1.ColumnA AND SubQuery.ColumnB = T1.ColumnB

I really tried to adapt your query to use this concept, but I am unclear on which columns to use. :-(

Terri|||just to give you a heads up on what i have while i try this out
i have 3 tables in the query
t_Location
t_Provider
and
t_Followup

t_Location is the hub, they all relate to this
t_Provider has all of the personal information, there are many of these related to one LocationID

t_Followup There is one of these per location in practice BUT there may be more

What i would like to do is display one record for each followup

i hope this helps you understand a little more
and while you are trying to grasp my gibberish i will see if i can get the last query you sent
if this gives you any new ideas please post them|||

SELECT MIN(t_Provider.ProviderID) AS ProviderID
FROM t_Followup INNER JOIN
 t_Location ON t_Followup.LocationID = t_Location.LocationID INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID
WHERE (t_Followup.RepID = 13) AND (t_Followup.FollowupDate <= '5/5/04') AND (t_Followup.RespondedTo = 0)
GROUP BY t_Followup.FollowupID

This gives me the results i would like but i would like many more columns to also be selected in the query (all the columns selected in the previous query i posted)
it looks like your solution would work but i am trying to grasp the concept of where i should put the next statement.

by the way there are 3 other look up tables i need to add to this statement|||Thanks you for the further explanation. With that query as the "guts" of your SELECT statement, you could expand it like this (I hope I got it right ;-) , there might be stuff that is redundant in there now):

SELECT
 t_Location.LocationID,
 t_Followup.FollowupDate,
 t_Provider.ProviderLName,
 t_Provider.ProviderFName,
 tlkup_FollowupType.FollowupType,
 tlkup_ActivityType.ActivityTypeName,
 t_Activity.ActivityDate,
 t_Provider.ProviderID,
 t_Followup.FollowupID
FROM
 tlkup_FollowupType
INNER JOIN
 t_Followup ON tlkup_FollowupType.FollowupTypeID = t_Followup.FollowupTypeID
INNER JOIN
 t_Activity ON t_Followup.ActivityID = t_Activity.ActivityID
INNER JOIN
 tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID
INNER JOIN
 t_Location ON t_Followup.LocationID = t_Location.LocationID AND t_Activity.LocationID = t_Location.LocationID
INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID
INNER JOIN
(
SELECT t_Followup.FollowupID, MIN(t_Provider.ProviderID) AS ProviderID
FROM t_Followup INNER JOIN
 t_Location ON t_Followup.LocationID = t_Location.LocationID INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID
WHERE (t_Followup.RepID = 13) AND (t_Followup.FollowupDate <= '5/5/04') AND (t_Followup.RespondedTo = 0)
GROUP BY t_Followup.FollowupID) AS SubQuery ON SubQuery.FollowupID = t_Followup.FollowupID AND SubQuery.ProviderID = t_Followup.ProviderID