MSSQL DISTINCT multiple fields throws up odd results

[ukchat]

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 this 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

[amitpatel66]

Could you please post the following for reference:

1. Sample data
2. The output data of your query
3. The sample output that you require

[ukchat]

Could you please post the following for reference:

1. Sample data
2. The output data of your query
3. The sample output that you require

Table data (narrowed down to only 1 assessment)

http://www.bksb.co.uk/bksb/data/tabledata.php

Query 1
http://www.bksb.co.uk/bksb/data/query1.php

Query 2
http://www.bksb.co.uk/bksb/data/query2.php

I have noticed that Microsoft SQL studio automatically take away my brackets from DISTINCT(filename) Is this normal? As this would select distinct everything which means ID is never the same so all the same.

I think I’ve kind of answered my own question but is there any way around this to select by just 1 distinct value? I’m use to MySQL and I would use unique(filename) even although mssql manager highlights this as a built in function it returns saying it’s not a function.

Regards
Liam

[amitpatel66]

I checked the sample data and I see that the ID is different for the same workbook. So your query is working perfectly since when you say distinct, it is getting distinct records because ID is different for each record of same workbook. Now when you say you want one record only each for workbook, then what should be the ID value.

For Eg: For workbook = 2, the ID are 36,37,38,39, so which value you want to display? If all the four, then the workbook and filename will repeat for all the records.

[ukchat]

I checked the sample data and I see that the ID is different for the same workbook. So your query is working perfectly since when you say distinct, it is getting distinct records because ID is different for each record of same workbook. Now when you say you want one record only each for workbook, then what should be the ID value.

Dor Eg: For workbook = 2, the ID are 36,37,38,39, so which value you want to display? If all the four, then the workbook and filename will repeat for all the records.

ID has been added as i have no other unique identifier.

This is why im wanting the distinct on filename as there could be multiple curriculum references to each file.

So all in all i need to do is select by distinct filename and i can sort out the rest.

regards
Liam

[amitpatel66]

ID has been added as i have no other unique identifier.

This is why im wanting the distinct on filename as there could be multiple curriculum references to each file.

So all in all i need to do is select by distinct filename and i can sort out the rest.

regards
Liam

Then your first query will give you the distinct file names group by work book.

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

[ukchat]

Then your first query will give you the distinct file names group by work book.

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

that wont let me select any extra fields e.g. ID and curric which i'll be needing to do.

So all in all i need a query that will select every field with a distinct filename.

when i added extra fields to your query i got this error.

Regards
Liam

[amitpatel66]

 
SELECT x.filename,x.workbook,cbw.ID FROM
(SELECT MAX(curricworkbooks.filename) AS "filename",workbook FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL GROUP BY workbook ORDER BY workbook ASC) x, curricworkbooks cbw WHERE x.workbook = cbw.workbook AND x.filename = cbw.filename
 
 

[ukchat]

 
SELECT x.filename,x.workbook,cbw.ID FROM
(SELECT MAX(curricworkbooks.filename) AS "filename",workbook FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL GROUP BY workbook ORDER BY workbook ASC) x, curricworkbooks cbw WHERE x.workbook = cbw.workbook AND x.filename = cbw.filename
 
 

Hi thanks but it's still not working i have posted returned data here: http://www.bksb.co.uk/bksb/data/query3.php


Qorkbook 1 is listed twice yet has the same filename.

regards
Liam

[amitpatel66]

Hi thanks but it's still not working i have posted returned data here: http://www.bksb.co.uk/bksb/data/query3.php


Qorkbook 1 is listed twice yet has the same filename.

regards
Liam

Ofcourse it will be listed twice becuase you have different ID value for both the records.Are you looking at something like this????

 
filename           Workbook                 ID
  1                        1                         20
NULL                  NULL                    22
 
 

[ukchat]

Ofcourse it will be listed twice becuase you have different ID value for both the records.Are you looking at something like this????

 
filename           Workbook                 ID
  1                        1                         20
NULL                  NULL                    22
 
 

All i want is a distinct filename and that will leave me with the records i need. of corse ID is different else it couldnt be a Primary Key. The ID is only their so i can pass it to a PHP script to download the correct file without passing the location of the file through GET or POST (all testing of SQL is being done direct in SQL Studio)

Do you understand what im after?

Regards
Liam

[amitpatel66]

All i want is a distinct filename and that will leave me with the records i need. of corse ID is different else it couldnt be a Primary Key. The ID is only their so i can pass it to a PHP script to download the correct file without passing the location of the file through GET or POST (all testing of SQL is being done direct in SQL Studio)

Do you understand what im after?

Regards
Liam

Are you looking at:

SELECT DISTINCT filename from table_name WHERE id = (pass ID value from PHP) ?

[ukchat]

Are you looking at:

SELECT DISTINCT filename from table_name WHERE id = (pass ID value from PHP) ?

No that is a seperate part what im doing is listing the unique filenames referenced by workbook number.

see attached picture

but this show 1 1 which i dont want it to as this is the same filename. How ever i cant list by distinct workbook as there are multiple files to each workbook in parts.

I need to select data where filename is unique. That's the extent of my situation.

Regards
Liam

[amitpatel66]

What i understand from the above picture is that for Numeracy Level1, it is present in so many work books. So you want the work books to be displayed once instead of repeating?

[ukchat]

What i understand from the above picture is that for Numeracy Level1, it is present in so many work books. So you want the work books to be displayed once instead of repeating?

yes----ish.. or a less there is a different filename for the workbook.

Trust me i can understand your difficulty in understanding this as i've had to go through the same process for over 200 workbooks.

the structure is totaly unlogical but unfortunatly i'm not the one that writes the workbooks, so i've had to just put up with it get it in database and now im trying to do this which i thought would be simple as im sure i can do this in MySQL by simply using unique(filename) then it would only select reccords with a unique filename but MSSQL is being a swine and saying everything has to be distinct rather than just 1 value.

regards
Liam

[amitpatel66]

yes----ish.. or a less there is a different filename for the workbook.

Trust me i can understand your difficulty in understanding this as i've had to go through the same process for over 200 workbooks.

the structure is totaly unlogical but unfortunatly i'm not the one that writes the workbooks, so i've had to just put up with it get it in database and now im trying to do this which i thought would be simple as im sure i can do this in MySQL by simply using unique(filename) then it would only select reccords with a unique filename but MSSQL is being a swine and saying everything has to be distinct rather than just 1 value.

regards
Liam

>>>im sure i can do this in MySQL by simply using unique(filename) then it would only select reccords with a unique filename

But again if you select ID in your query, it would give you all the records in which the filename and workbook will repeat. If you DO NOT select the ID column then you will get the UNIQUE filenames for each workbook as I did earlier using GROUP BY workbook clause.

[ukchat]

>>>im sure i can do this in MySQL by simply using unique(filename) then it would only select reccords with a unique filename

But again if you select ID in your query, it would give you all the records in which the filename and workbook will repeat. If you DO NOT select the ID column then you will get the UNIQUE filenames for each workbook as I did earlier using GROUP BY workbook clause.

The thing is i need the ID to pull out the correct workbook. I suppose i could work arround it by running a query to select IF FROM table WHERE filename = 'filenamevalue'

was just hopinf for somthing all in 1 but will give this a blast.

EDIT: Actually that wont work as when using DISTINCT and putting workbook as a field it select distinct workbook too! which means i cant have multiple workbooks returned where there are more than 1 file.


regards
Liam

[amitpatel66]

The thing is i need the ID to pull out the correct workbook. I suppose i could work arround it by running a query to select IF FROM table WHERE filename = 'filenamevalue'

was just hopinf for somthing all in 1 but will give this a blast.

regards
Liam

Ok. So from Front end, you will be passing ID as input parameter value is it? If yes, then using ID value, its simple to get the corresponding filename.

Could you post what are the columns you need in your select query from the below:

1. Filename,workbook,ID? -- If yes, then MAX(ID) to get a single record for each work book?

2. Filename,workbook -- Need to take MAX(filename) group by workbook which will give distinct records

3. Filename - DISTINCT filename fomr corresponding table

And it would be great if you can provide the way you want your output to be (sample output) for reference?