select distinct but return all records

hi there,

I have a database that has about 6 columns, such as firstName, familyName etc and also a column called ticketNumber. the unique id is called id. I want to select records using DISTINCT for the ticketNumber, but return all records including id. using DISTINCT on all records returns everything still because as id is always unique, every row is unique. i tried:

SELECT * FROM competition_entries WHERE ticketNumber IN (SELECT DISTINCT ticketNumber FROM competition_entries)

but that still select everything because it returns mutliple rows for each distinct ticketNumber is selects from the subquery!

any help would be great, thanks

mark.

Hi Mark -

Sorry, but I'm having trouble understanding your goal.

You mention that you want to return all records, but then also mention that you want to only select DISTINCT on the ticketNumber field. These two cases are mutually exclusive, and cannot exist at the same time, as far as I know. This is because SELECT DISTINCT doesn't care what row the value is from, it's just returning common values as one result record. When you add the ID field to a SELECT DISTINCT, it (as you noted) makes each resulting record unique, so you'll get all records.

As far as I know, there's no way to do what you're explaining. Perhaps if you could wrap some context around what you're trying to do, one of the resident SQL experts here might be able to offer a better solution? Also, this is more of a SQL question than a CF one, so you might try posting on that board as well.

Hope that helps.

Brad

Ok if im correct all you are wanting to do is select records from a database that have a specific ticket number right?

You dont use the DISTINCT function for that, you would use <cfqueryparam>.

If this is comming from a form, you would do somthing like :

SELECT * FROM competition_entries WHERE ticketNumber LIKE (<cfqueryparam value="#form.value#">)

You could even drop the <cfquery> and just use standard sql,

SELECT * FROM competition_entries WHERE ticketnumber LIKE '#form.value#'

If this is comming from an array or list your going to have to use <cfqueryparam> and set it to list and use a dilimter of ,

You wont use IN because you are not selecting records in, you want to select records LIKE people often get them mixed up because they are quite similer.


PROGRAMMING TO PRINCE - - Party like its 1999
-------------------------------------------------------------
CFML PROGRAMMER (WWW.COLDFUSIONZONE.COM)

Hi guys,

The thing is that i don't know what the ticket number is before I do my select. Basically there's a system where for each entry made by a customer of certain ticket types you get 4 competition chances, some 6 chances and others just the one, so there are 6 entries of almost identical data for some people and just one for others. I'm trying to get just the unique ticket numbers, but along with all the other data that goes with it. For example if i have these rows:

id firstName familyName ticketNumber
33 Joe Bloggs 1234
34 Joe Bloggs 1234
35 Bob Smith 9999
37 Some Guy 8888
38 Some Guy 8888
39 Some Guy 8888


i need to get 3 records returned, but with their id numbers, so i can sort them into a semblance of chronological order, but if i use distinct then the id numbers mean that every row is returned because SQL sees the id/ticketNumber combo as always being unique.

Cheers,

mark.

I don't see how you could expect to get anything else. The subquery says "select all unique ticket numbers", and then the outer query says "select records from the table where the ticket number is present in this list: select all unique ticket numbers". How could you not expect get back every record?

yes, i know. i'd like to get back only the first record for each ticket number, obviously this SQL isn't going to do it, so i was hoping someone could help me out by showing me some SQL that did...

Why not just say:

select distinct ticket_no, name
from competition_entries

which would give each unique combination of ticket number and name?

I'm hoping to get the Id field as well - but if i say:

select distinct id, ticket_no, name
from competition_entries

i get everything because the id is unique.

This just seems strange to me, but let me be sure I understand. You want all unique names and ticket numbers, and then just ONE of the id's? I don't see what good it would do for you to have just a random choice of id for that name and ticket number...

I need to get the ids because there was not timestamp added to the database and thats the only way i can get it into some kind of order - a fudge i know, but thats the only way i can do it... i thought that id be able to use:

SELECT DISTINCT ticket_no, name FROM competition_entries ORDER BY id

but that throws an error.

Could you not just order it by name?

Maybe it would help if you could post up a small "grid" of example data in the database, maybe for 2 or 3 names, and then show what you want to get in the results?

SELECT h1, MAX(h2), MAX(h3)
FROM Table1
GROUP BY h1

so h1 would be your ticket_number and h2 and so on would the rest of your attributes and Table1 would be competition_entries