Get entire record on SELECT DISTINCT?

Thanks for the response, but this query returns duplicates on my url column.  What I need to do is get distinct values on the url column, but also return the whole record with the distinct url value.

Thats not possible. The reason there are duplicate urls is because some other data in other columns is different between rows.

searchei,

I am assuming AutoInc is an identity column.  If this is the case, try:

SELECT t1.*
FROM YourTable t1 INNER JOIN
    (SELECT t2.Url, MIN(t2.AutoInc)
    FROM YourTable t2
    GROUP BY t2.Url) t3 ON t1.Url = t3.Url AND t1.AutoInc = t3.AutoInc

Regards,

Patrick

select a.*
 from yourtable as a
 Inner Join (select url,max(autoinc) as autoinc from yourtable group by url) as B
 on a.autoinc=b.autoinc
 order by url

will get the latest row for each distinct url on the table assuming autoinc is an identity column...

otherwise you need to described to us how you wish to choose a row for each distinct url value...
the first one the last one some other criteria... and which columns that choice should be made upon...  

if you have multiple rows with the same URL you need to provide us the criteria which record you want to return. Is it the one with the max value in the autoinc field, min value, or something else?

I am sorry I am not sure if I understand the sql statements you guys are using (I am very new to sql).  Some of the statements look as if you are making a query to more than one table, am I wrong?  Or I just don't know enough about sql syntax.  

 Anyway, if I have a table named ADS and I am trying to get distinct values in column URL with the identity's column being AUTO_INC, and I want to return the full row for each distinct value in the column URL(I dont care which row is returned)

Would my syntax be like this?

SELECT t1.*
FROM ADS t1 INNER JOIN
    (SELECT t2.URL, MIN(t2.AUTO_INC)
    FROM ADS t2
    GROUP BY t2.URLl) t3 ON t1.URL = t3.URL AND t1.AUTO_INC = t3.AUTO_INC

Thanks again

> Some of the statements look as if you are making a query to more than one table, am I wrong?
no, to the same table, but with a different alias name (self join).

in regards to the syntax, your query is almost correct.

SELECT t1.*
FROM ADS t1
INNER JOIN    (SELECT t2.URL, MIN(t2.AUTO_INC) AUTO_INC
        FROM ADS t2
         GROUP BY t2.URL
     ) t3
  ON t1.URL = t3.URL
AND t1.AUTO_INC = t3.AUTO_INC

alternatively:
select t.*
from ADS t
where t.autoinc = ( select min(i.autoinc) from ADS i where i.url = t.url )

That works perfectly, thank you for all replies

searchei,

I am puzzled.  Meaning absolutely no disrespect to angelIII, his answer which you selected
used exactly the same logic that I posted more than 12 hours earlier.

Please explain why you did not give me at least a split.

Regards,

Patrick

I am sorry Patrick, I didn't realize what the point thing was all about.  I am used to other message boards and just happened to click on the last message because that was the one I cut the code from.  Is there a way I can give you points?  Or, I can post another question and give you the points also, this way

searchei,

No worries.  One thing you can do is to post in Community Support asking to reopen
this question so that you can do a split.  This is a fairly common occurrence.  If you
do this (and it is entirely up to you, of course), please do make sure to include the
URL for this question in your post.  CS "questions" are all 0 points.

Then, once this question is reopened, you can select an answer and/or do a split
as you see fit.

Regards,

Patrick

Patrick,

I have sent a support request to have the question re-opened and the points split.  Again I am sorry and thank you for your help.

Chris

searchei,

can you explain the logic behind this query? I can't think of an example where I would need only one of different records but don't really care which one.