Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Question Unanswered: Getting unique rows

    Is it possible to use the DISTINCT clause on just one field in the SELECT statement?

    The following SQL statement causes an error:

    SELECT DISTINCT appt.ref, appt.notes FROM Appointments appt

    ...because DISTINCT can't be used on the notes field as it of type 'text'.

    How can I focus the DISTINCT keyword on just the ref field?

    (I know ref is the primary key, so this example wouldn't need the DISTINCT keyword, but I've simplified a much more complex statement)


    Paul

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Does this work?

    Code:
    SELECT ref, notes FROM Appointments
    WHERE ref IN (SELECT DISTINCT ref From Appointments)

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    use group by, you should get same result.

    select ref, notes from ...
    group by ref, notes

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    OK - trying to use 'GROUP BY', but it comes up with the following error:

    Column 'XXX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    The XXX is the name of the field and it brings this up for any of the SELECT fields that aren't also in the GROUP BY clause. I always have this problem when using GROUP BY.

    Any way around it?

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    Actually, let me give you the full SQL query:

    SELECT appt.ref, memb.firstName, memb.surname, note.visible, note.date, note.notes, appt.date, note.new FROM touchMembers memb, touchPractitioners pract, touchNotes note, touchAppointments appt WHERE appt.practitioner=2 AND note.appointment=appt.ref AND memb.ref=appt.member ORDER BY note.date DESC

    If I run the query as it is, it returns three records (all with the same appointment ref).

    Any ideas why?


    More details:

    There are a number of records in the touchMembers and touchPractitioners tables (a member is booked into an appointment - a practitioner will treat them).

    The touchAppointments table has three records, all relating to practitioner 2.

    The rows in the touchNotes table relate to one (and only one) appointment. There may be no notes for a given appointment. There is currently only one record in the table, for appointment 2.

  6. #6
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    you could use the CAST() function to convert the long text field to a shorter text field (e.g. 255 chars) as a copy of that field but displaying the original field.

    this should allow the DISTINCT command to work as it'll be working on a field which is now shorter and thus should allow it to work ok,


    just an idea.

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by paulbrooks
    Actually, let me give you the full SQL query:

    SELECT appt.ref, memb.firstName, memb.surname, note.visible, note.date, note.notes, appt.date, note.new
    FROM touchMembers memb, touchPractitioners pract, touchNotes note, touchAppointments appt
    WHERE appt.practitioner=2 AND note.appointment=appt.ref AND memb.ref=appt.member
    ORDER BY note.date DESC
    Why is touchPractitioners in your FROM-clause? You are not selecting any fields from it, and it doesn't appear in your WHERE-clause. This leads to a Cartesian product: the row you want is repeated for every row in touchPractitioner. So my guess is that touchPractitioners has three rows...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •