PHP/MSSQL DISTINCT query

[shocker-z]

Hi,

 

Brief:

I have a database table with the following columns: ID, curric, assessment, topic, workbook and filename

This table is used to pull out workbooks for certain assessment levels of the curriculum. What im trying to do is select all records from curricworkbooks table where the filename field is distinct and assessment = 'Literacy L1' My current query below.

 

SELECT distinct(curricworkbooks.filename),workbook FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL ORDER BY workbook ASC

 

This works fine but i also need to pull out the ID yet when i change my query to:

 

SELECT distinct(curricworkbooks.filename),workbook,ID FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL ORDER BY workbook ASC

 

It doesn't filter by distinct and therefore I end up with multiple results for the same workbook.

 

The reason for the duplicate workbooks is that there are different curriculum references.

 

Regards

Liam