MSSQL DISTINCT multiple fields throws up odd results

[shocker-z]

I.m creating a dynamic query to pull out workbooks from my database the table structure is below.

 

Table: curricworkbooks

Columns: ID, curric, assessment, topic, workbook, filename

 

Example data

[iD]	[curric]	[assessment]	[topic]		[workbook]	[filename]
1	N1/E1.1 	Numeracy E1	Count		1		workbooks/Num Entry 1/Unit 1/04 N1E1.1-3 Worksheets Num.pdf
10	MSS1/E1.6	Numeracy E1	Capacity 	13		workbooks/Num Entry 1/Unit 13/04 MSS1E1.6 Worksheets Num.pdf
100	MSS1/L1.7	Numeracy L1	Conversions	14		workbooks/Numeracy Level 1/wkbk_14 AoN L1 2D 3D & Scale.pdf

 

So my system will output each workbook in a table which works fine. using thsi query

 

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

 

but i need to also select the ID field from the table, so i thought the query would be somthing like this:

 

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

 

But when i use that query i get non distinct results. I need the distinct on filename as there can be multiple curriculum reference per each workbook which can relate to the same file (Trust me just beleive me on this on the structure is totaly unlogical but it's data that im unable to change.)

 

All support greatlt appreciated.

 

Posted simular to this yesterday but think the site got restored again.

 

regards

Liam