SQL SELECT DISTINCT (and not distinct at the same time)

[bgowland]

I need to select all distinct programme names from the SCHEDULED_RECORDING column BUT I also need to return a unique identifier at the same time. Confused? Join the club.

The problem is that SELECT DISTINCT works on multiple columns so the following actually produces multiple instances of the 'name' field...

Code:

SELECT DISTINCT oid AS _id, name FROM SCHEDULED_RECORDING

The problem is the 'oid' field is always unique as it's an auto-increment and so DISTINCT is matching on that and returning each entry for the 'name' field.

All I need is one instance of each 'name' and an associated oid - I don't care which oid I get. I've tried WHERE IN and a separate SELECT as well as trying JOIN (LEFT and INNER) but at this point my SQL is very thin.

All help appreciated.

Cheers,
Brian

[mvallevand]

If you don't care which OID

SELECT OID FROM SCHEDULED_RECORDING
GROUP BY NAME

Martin

[bgowland]

Thank you!!!! I can't believe it is so simple.

You missed out selecting 'name' as well but I picked up on that.

Code:

SELECT oid AS _id, name FROM SCHEDULED_RECORDING GROUP BY name

Works perfectly.

No I don't care which oid, it's an Android Cursor thing - a query must return a field/column called '_id' and it must be unique. Normally not a problem but I couldn't figure that one out. Thanks again.

Cheers,
Brian