SQL and DISTINCT
Author
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?
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?
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.
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.
Author
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.
There is probably an easier way than this but it should work (not havent tested it).
Been a while since I have done sql so maybe that isnt right.
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.
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)?
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)?
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.
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.
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.
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.
I could suggest:
Of course, this might not do what you want, but I still don't know what you want.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
