Return all fields with SELECT DISTINCT

studiobanks

OK I need to return all the fields in a record while only selecting records that have a distinct combination of fields.

SELECT DISTINCT DATE, VENUE_ID FROM TICKETS WHERE TYPE_ID =\"$type_id\"

This will return records with a distinct combination of date and venue_id. However I need it to return all the feilds of the records returned not just the date and venue_id feilds.

Any suggestions?

nnichols

SELECT DISTINCT (DATE, VENUE_ID), other_cols FROM TICKETS WHERE TYPE_ID ="$type_id"

This will select all the listed columns where DATE & VENUE_ID combination is unique

Hope this helps 😉

studiobanks

SELECT DISTINICT (DATE, VENUE_ID), TICKET_ID, NAME FROM TICKETS WHERE TYPE_ID =\"$type_id\"

This is not working. ?????

Lars_Berg

If you have the following data

date Venue_id c1

xxxx yyyyyyyyy 12
xxxx yyyyyyyyy 13

Which value for c1 should be returned?

So give some sample data and the expected result.

studiobanks

ok.

DATE, VENUE_ID, TICKET_ID, and NAME are the feilds that I need returned. I need it to only return records with a DISTINCT combination of DATE and VENUE_ID WHERE TYPE_ID is what is searched for.

If this is my table:

TICKET_ID | DATE | VENUE_ID | NAME | TYPE_ID

01 | 01-01-2003 | 01 | Concert 1 | 01
02 | 01-01-2003 | 01 | Concert 1 | 01
03 | 02-02-2003 | 02 | Concert 2 | 01
04 | 02-02-2003 | 02 | Concert 2 | 01
05 | 03-03-2003 | 03 | Sporting 3 | 02
06 | 03-03-2003 | 03 | Sporting 3 | 02

and someone searches for TYPE_ID=01 it should return:

TICKET_ID | DATE | VENUE_ID | NAME

02 | 01-01-2003 | 01 | Concert 1

04 | 02-02-2003 | 02 | Concert 2

When I tried the code posted by "nnichols" php was unable to query the database.

tomhath

SELECT MAX(TICKET_ID), DATE, VENUE_ID, NAME
FROM TICKETS
WHERE TYPE_ID ="$type_id\"
GROUP BY DATE, VENUE_ID, NAME

nnichols

Sorry about that. My head wasn't screwed on properly. I hope it is now, otherwise I'll feel a right pratt if I get it wrong again. Try this -

SELECT MAX(TICKET_ID), DATE, VENUE_ID, NAME
FROM TICKETS
WHERE TYPE_ID =1
GROUP BY DATE, VENUE_ID, NAME

It worked with your simple sample data.

Hope this works :rolleyes:

nnichols

Damn. He beat me to it 🙂

studiobanks

Thanks Guys!!!!
Much love form Charlotte.
Yall are awesome!!