Select Distinct...?

[christrinder]

I was hoping somebody could help me understand the DISTINCT function in ASP. I fully understand its purpose, and I want to use in the following select statement, but I want it to select DISTINCT CompanyNames.

SELECT DISTINCT CompanyID, CompanyName

I have tried rearranging the order, but it doesn't seem to do the trick. Can there be only one field in the statement?

Thanks in advance for your help.
Chris

[Spudhead]

SELECT DISTINCT CompanyName, CompanyID

should, AFAIK, select only distinct company names and their corresponding ID's. What is it returning for you?

[christrinder]

It repeats like for like CompanyNames, basically it selects everything... there is no DISTINCT screening at all.

[Roelf]

the distinct keyword ensures a unique record, where the uniqueness is determined for all given column-names. I may hope you CompanyID column is unique, so it should indeed return all records.
Imagine it should work the way you want to, and the query returns companynames where there are duplicate values. For which instance of the name do you want the ID returned? I dont know, the database also doesnt. so it is not possible this way

[raf]

the distinct keyword ensures a unique record, where the uniqueness is determined for all given column-names. I may hope you CompanyID column is unique, so it should indeed return all records.

Is that so? Not on a combination of the two? Or is that only with DISTINCTROW

Anyway.
SELECT DISTINCT idvariable, namevariable FROM table GROUP BY idvariable, namevariable
Has always worked for me. (if the companyname and ID are interdependent, which normally should be ! I mean, in 2 records this firmname1 - id1, firmaname1 - id2 should never occur)

[Roelf]

thats what i meant (english is not my native language) the combination of all given columns must be unique. So if you select an id column, every record should give a unique combination

[raf]

Well, whe both speak dutch (flemish for me).

But enough chitchat :D I'm putting my monney on Christrinder forgetting the 'group by' or that the CompanyID - CompanyName relation is somewhat unlogically mixed.

[christrinder]

You boys have been busy! Sorry I haven't checked back sooner... I've been making the most of this extra long, Bank Holiday weekend! OK, so my original post may have been a bit misleading. The table CompanyID is obvioulsy unique (an autonumber), but the text field CompanyName is not always unique, it is difficult to explain why, and is kept in the same table. For instance, the table may include something like:

1 Bmw
2 Jaguar
3 Porsche
4 Jaguar
5 Land Rover
6 Jaguar

I want to produce a drop-down that includes the CompanyID in the value, and the CompanyName, but only where the CompanyName is distinct. I could do a subquery within a distinct CompanyName query to get the CompanyID, but I figure there's probably a way to do it in one query?

Any ideas?

[raf]

1 Bmw
2 Jaguar
3 Porsche
4 Jaguar
5 Land Rover
6 Jaguar

I want to produce a drop-down that includes the CompanyID in the value, and the CompanyName, but only where the CompanyName is distinct.
:confused:
What would the dropdown then look like?
Would you have three options with Jaguar as label? Or if you have only one option with the Jaguar label, what should then be the value for that option. :confused:

Can you give an example of what the optionslist should look like?

[christrinder]

Hi,

Its sounds bizarre I know, but it I'm using the business' existing database, and they have multiple franchises, using different ID's. I would want the drop down to look like...

<option value="1">Bmw</option>
<option value="2">Jaguar</option>
<option value="3">Porsche</option>
<option value="5">Land Rover</option>

Hope that makes sense... I realise it would make little use for most applications, but I do need it to do this.

Thanks,
Chris

[raf]

Ahhhhhh! Those existing designs. Completely wrong approach !! Need to change everything !! :(

Now. Practical and instant quick and dirty sollutions. (Which to choose depends on the number of records and the number of doubles in the namecolumn)
Option 1 : create a table with a distinct on the Companyname. Have an autonumber in this table. Build the dropdown based on this table. (You can also copy the original table and remove the doubles (woun't take that long i suppose)
Option 2 : run this
SELECT DISTINCT idvariable, namevariable FROM table GROUP BY idvariable, namevariable ORDER BY namevariable asc
in your ASP-page, in the loop that builds the options, have a check if the value for namevariable is the same as the record before that one. So if you have doubles, only the first record with that name will be used to generate an option. if you include the idvariable in the order by clause, you can decide if it needs to be the record with the highest or lowest id that needs to be used.

I have no idea how you decide which of the duplicate records you use to build an option + what you do with the value for that option. But anyway, it's poor db design.