Get entire record on SELECT DISTINCT?

I am quite new to sql and I am not sure how to develop this query.  I am trying to get the entire record based on a distinct column.

For instance I have a table with about 15 columns in it.  

AutoInc
Url
col3..
col4..
etc..

I know select DISTINCT url from table websites will return all the distinct values in the column url.  What I need is the entire record.  How do you form this query where I get the entire record based on the distinct values of the url column?

Hope this makes sense.

Thanks
searcheiAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
SELECT DISTINCT *
FROM YourTable
0
 
searcheiAuthor Commented:
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.
0
 
derekkrommCommented:
Thats not possible. The reason there are duplicate urls is because some other data in other columns is different between rows.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Patrick MatthewsCommented:
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
0
 
LowfatspreadCommented:
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...  
0
 
bamboo7431Commented:
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?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
as noted above, when having multiple rows with the same url value, what row do you want to get.
assuming like matthewspatrick that you have a AutoInc column:

select t.*
from yourtable t
where t.autoinc = ( select max(i.autoinc) from yourtable i where i.url = t.url )

for the performance, an index on url + Autoinc column will be best...
0
 
searcheiAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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 )



0
 
searcheiAuthor Commented:
That works perfectly, thank you for all replies
0
 
Patrick MatthewsCommented:
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
0
 
searcheiAuthor Commented:
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
0
 
Patrick MatthewsCommented:
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
0
 
searcheiAuthor Commented:
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
0
 
bamboo7431Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.