Sign in to follow this  
programmermattc

SQL and DISTINCT

Recommended Posts

I'm trying to get this query to select a distinct record using the SQL DISTINCT keyword though I need to select multiple fields and only apply the distinct to the first field. (I have an ID and only want one of that id with multiple other fields after it) I can't think of how to do it either from lack of knowledge or lack of remembrance. Anyone know how to do it?

Share this post


Link to post
Share on other sites
ToohrVyk    1595
Quote:
Original post by programmermattc
I have an ID and only want one of that id with multiple other fields after it


So, you have several rows with the given ID, and only want one? Which?

Share this post


Link to post
Share on other sites
daerid    354
TOP 1 possibly?

Are you looking for a specific row? If so, just search for the values in whatever combination of columns constitutes your primary key.

If you're looking for any record with that ID, but just a single one, TOP 1 will do. If you need a random one, we'll need to know what dialect of SQL you're using.

Share this post


Link to post
Share on other sites


I need to get distinct records in that. I should technically get 3 records, one for 55, one for 61, and one for 64. Above is what I get using a distinct and selecting those three fields. The problem is that it's finding distinct between the other fields other than the first one.

Share this post


Link to post
Share on other sites
cmptrgear    306
There is probably an easier way than this but it should work (not havent tested it).


select col1, col2, col3 from table1 where col1 in (select distinct col1 from table1)



Been a while since I have done sql so maybe that isnt right.

Share this post


Link to post
Share on other sites
Michalson    1657
As already asked, *which* one of those two records with the same ID do you want? SQL is a functional language - that means you tell the computer what you want the final result to be, and it figures out the steps to do it (languages like C work the other way - you tell the computer the steps, but only you understand what those steps will accomplish). If you don't know what you want, SQL isn't going to be able to tell you.

So, you have these two records:

55 - 5 - Winter.jpg
55 - 7 - Sunset.jpg

Which one do you want, and why (what criteria led you to picking one or the other)?

Share this post


Link to post
Share on other sites
Xai    1838
programmermattc - what you are discribing is not making sense ...

distinct can ONLY be used to return the set of distinct entries it finds, it CANNOT be used to return those, plus other stuff - else it would not be distinct, or it would be doing a top(1) type operation, which is NOT available or automatic in this situation.

if you are doing AGGREGATION, then you can get a behavior similar to what you seem to be thinking about, since you would put the elements you want to use as row keys as the "group by" columns ... then your other returned columns could be calculations, such as sums, average, etc ...

top, distinct, group by, inner join, subselect, and where clauses are the 3 methods of limiting a result set to smaller than it started.

join is the primary means of expanding a set with another set.

so you might need to build complex results by basically creating 2 views and joining them (a limiting view and a value adding view)

tell us what you are trying to acomplish - not what method you think you should use.

Share this post


Link to post
Share on other sites
Xai    1838
to emphasize, things like this:

select col1, col2, col3 from table1 where col1 in (select distinct col1 from table1)

would just be the same as:

select col1, col2, col3 from table1

because EVERY column value would be in the list of distinct columns - so every row would match.

Share this post


Link to post
Share on other sites
ToohrVyk    1595
I could suggest:

SELECT * 
FROM table1
WHERE (col1,col2) IN
(SELECT col1, MIN(col2)
FROM table1
GROUP BY col1)


Of course, this might not do what you want, but I still don't know what you want.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this