• 0

[SQL] Distinct on just one column


Question

Hey guys,

I usually get what I want in SQLite, but this time around, the language beats me.

Basically I have a table where the TYPE is repeated often, but where the ID is unique. I want to return unique TYPEs with their corresponding, random, ID.

Here's my (simplified) data :

ID TYPE
1  CHARACTER
2  CHARACTER
3  PLACE
4  CHARACTER
5  OBJECT
6  OBJECT
7  PLACE
8  CHARACTER
9  PLACE
10 PLACE

For instance, this could be a perfect result :

ID TYPE
7  PLACE
2  CHARACTER
6  OBJECT

Thanks a lot in advance

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hey guys,

I usually get what I want in SQLite, but this time around, the language beats me. Basically I have a table where the TYPE is repeated often, but where the ID is unique. I want to return unique TYPEs with their corresponding, random, ID.

Here's my data :

ID TYPE
1  CHARACTER
2  CHARACTER
3  PLACE
4  CHARACTER
5  OBJECT
6  OBJECT
7  PLACE
8  CHARACTER
9  PLACE
10 PLACE

For instance, this could be a perfect result :

ID TYPE
7  PLACE
2  CHARACTER
6  OBJECT

What ID do you want? Just any ID with that type?

Link to comment
Share on other sites

  • 0

Thanks, so far this is simple and it’s working.

Now, is there a possibility to return a RANDOM ID instead of the MIN ?

You could use the RAND function, and perhaps do a greater than less than on the result..

perhaps something like..

select
  case when rand() > .5
    then x.MinID
    else x.MaxID
  end as ID
  ,x.Type
from (select
        min(id) as minID
        ,max(id) as maxID
	,type
     from
        myTable
     group by type) x

this is completely untested though.. i don't even know off hand what the range of results from RAND() is.. i'm assuming its between 0 and 1 though..

Link to comment
Share on other sites

  • 0

Thanks, so far this is simple and it?s working.

Now, is there a possibility to return a RANDOM ID instead of the MIN ?

A random id completely or a random id that corresponds to one of the valid ids for that distinct type? There are ways to do both.

If its the former, is there any requirement that the id not be in use on a record with a different value for 'type?'

If it's the latter I'm getting a feeling that you might be doing something that should probably be re-thought and structured differently. (If you have id's, its likely because you have a somewhat normalized/relational schema, and if that's the case, why have multiple instances of the same 'type'?)

Link to comment
Share on other sites

  • 0

A random id completely or a random id that corresponds to one of the valid ids for that distinct type? There are ways to do both.

If its the former, is there any requirement that the id not be in use on a record with a different value for 'type?'

If it's the latter I'm getting a feeling that you might be doing something that should probably be re-thought and structured differently. (If you have id's, its likely because you have a somewhat normalized/relational schema, and if that's the case, why have multiple instances of the same 'type'?)

The latter. In fact, I have two tables. One with unique TYPES and one with unique IDs and those unique IDs are linked to TYPES, so that?s why it appears more than once. So yes the data is all normalized as you?re proposing, but I simplified by removing the other table, because it can be obtained just with

SELECT DISTINCT type
FROM 'mytable'

For CHARACTER, I could assign IDs 1, 2, 4 or 8. Others would be invalid.

The best solution would be to choose a random ID (1, 2, 4 or 8), not the min (1) or max (8). Otherwise, I have data in my table that will never appear and this is not what I target in my code.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.