By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

MSSQL DISTINCT multiple fields throws up odd results

P: 15
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

Expand|Select|Wrap|Line Numbers
  1. [ID]    [curric]    [assessment]    [topic]        [workbook]    [filename]
  2. 1    N1/E1.1     Numeracy E1    Count        1        workbooks/Num Entry 1/Unit 1/04 N1E1.1-3 Worksheets Num.pdf
  3. 10    MSS1/E1.6    Numeracy E1    Capacity     13        workbooks/Num Entry 1/Unit 13/04 MSS1E1.6 Worksheets Num.pdf
  4. 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.

Expand|Select|Wrap|Line Numbers
  1. 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:


Expand|Select|Wrap|Line Numbers
  1. 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
Mar 11 '08 #1
Share this Question
Share on Google+
17 Replies


amitpatel66
Expert 100+
P: 2,367
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
Mar 11 '08 #2

P: 15
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
Mar 11 '08 #3

amitpatel66
Expert 100+
P: 2,367
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.
Mar 11 '08 #4

P: 15
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
Mar 11 '08 #5

amitpatel66
Expert 100+
P: 2,367
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(curricworkbooks.filename),workbook FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL GROUP BY workbook ORDER BY workbook ASC
  2.  
Mar 11 '08 #6

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

Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(curricworkbooks.filename),workbook FROM curricworkbooks WHERE assessment = 'Literacy E2' AND workbook IS NOT NULL GROUP BY workbook ORDER BY workbook ASC
  2.  

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
Mar 11 '08 #7

amitpatel66
Expert 100+
P: 2,367
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT x.filename,x.workbook,cbw.ID FROM
  3. (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
  4.  
  5.  
Mar 11 '08 #8

P: 15
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT x.filename,x.workbook,cbw.ID FROM
  3. (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
  4.  
  5.  

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
Mar 11 '08 #9

amitpatel66
Expert 100+
P: 2,367
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????

Expand|Select|Wrap|Line Numbers
  1.  
  2. filename           Workbook                 ID
  3.   1                        1                         20
  4. NULL                  NULL                    22
  5.  
  6.  
Mar 11 '08 #10

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. filename           Workbook                 ID
  3.   1                        1                         20
  4. NULL                  NULL                    22
  5.  
  6.  
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
Mar 11 '08 #11

amitpatel66
Expert 100+
P: 2,367
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) ?
Mar 11 '08 #12

P: 15
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
Mar 11 '08 #13

amitpatel66
Expert 100+
P: 2,367
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?
Mar 11 '08 #14

P: 15
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
Mar 11 '08 #15

amitpatel66
Expert 100+
P: 2,367
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.
Mar 11 '08 #16

P: 15
>>>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
Mar 11 '08 #17

amitpatel66
Expert 100+
P: 2,367
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?
Mar 11 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.