[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get entire record on SELECT DISTINCT?

Posted on 2007-07-20
16
Medium Priority
?
1,460 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:searchei
  • 5
  • 4
  • 2
  • +3
15 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 252 total points
ID: 19537205
SELECT DISTINCT *
FROM YourTable
0
 

Author Comment

by:searchei
ID: 19537277
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 19537282
Thats not possible. The reason there are duplicate urls is because some other data in other columns is different between rows.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19537310
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19537457
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
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19538356
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 248 total points
ID: 19538399
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
 

Author Comment

by:searchei
ID: 19540398
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19540438
> 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
 

Author Comment

by:searchei
ID: 19540484
That works perfectly, thank you for all replies
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19541110
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
 

Author Comment

by:searchei
ID: 19541183
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19541229
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
 

Author Comment

by:searchei
ID: 19541254
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
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19542422
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question