NextPVR Forums
  • ______
  • Home
  • New Posts
  • Wiki
  • Members
  • Help
  • Search
  • Register
  • Login
  • Home
  • Wiki
  • Members
  • Help
  • Search
NextPVR Forums Public Developers v
« Previous 1 … 11 12 13 14 15 … 93 Next »
SQL SELECT DISTINCT (and not distinct at the same time)

 
  • 0 Vote(s) - 0 Average
SQL SELECT DISTINCT (and not distinct at the same time)
bgowland
Offline

Posting Freak

West Yorkshire, UK
Posts: 4,583
Threads: 384
Joined: Dec 2004
#1
2012-01-22, 09:48 PM
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. Big Grin

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
Online

Posting Freak

Ontario Canada
Posts: 52,866
Threads: 954
Joined: May 2006
#2
2012-01-22, 10:00 PM
If you don't care which OID

SELECT OID FROM SCHEDULED_RECORDING
GROUP BY NAME

Martin
bgowland
Offline

Posting Freak

West Yorkshire, UK
Posts: 4,583
Threads: 384
Joined: Dec 2004
#3
2012-01-22, 10:19 PM
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. Smile

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
« Next Oldest | Next Newest »

Users browsing this thread: 1 Guest(s)



Possibly Related Threads…
Thread Author Replies Views Last Post
  Extending end time. mvallevand 5 4,074 2018-10-17, 01:21 PM
Last Post: jcole998
  Extending recording time mvallevand 2 2,454 2014-09-14, 01:04 AM
Last Post: sub
  Time to make Touch web Service less experimental fred250 106 28,050 2014-06-15, 09:13 PM
Last Post: fred250
  <StartTime> and <EndTime> time formats? spinnaker 3 2,471 2013-10-25, 01:48 AM
Last Post: spinnaker
  Date Time display Northpole 0 1,439 2011-08-01, 04:28 PM
Last Post: Northpole
  N-PVR db3 EPG_EVENT time format question bgowland 3 2,075 2011-03-12, 05:26 AM
Last Post: bgowland
  Suppressing the Date/Time field from global.xml ACTCMS 7 3,153 2010-11-18, 01:36 AM
Last Post: ACTCMS
  Weather Doppler Radar missing time 4 hours Snooze 0 1,313 2009-02-02, 07:00 PM
Last Post: Snooze
  Select capture source khaver 3 1,865 2008-12-15, 02:20 AM
Last Post: khaver
  Current time in plugins? bgowland 4 2,112 2008-05-12, 03:19 AM
Last Post: bgowland

  • View a Printable Version
  • Subscribe to this thread
Forum Jump:

© Designed by D&D, modified by NextPVR - Powered by MyBB

Linear Mode
Threaded Mode