Conflict with select distinct and order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Conflict with select distinct and order by

Author

Topic

Celtics3410
Starting Member

11 Posts

Posted - 2003-07-02 : 08:56:18

I can't get this to work, and no one at work can figure it out either. It involves a SELECT DISTINCT on a text field "SCRNm". There are multiple instances throughout the database, and a count must be kept of each instance. Some may only have one or two throughout it, some might have 50 or 60.

The important thing is to do an ORDER BY, using the ID column. ID is also the Primary Key. I originally had it set up to go in descending order, but if it won't work in that order for some odd reason will work in ascending order, then that is fine.

Here are two queries I have tried. The first results in an error saying "ORDER BY clause (ID) conflicts with DISTINCT". The second one seemed to work, but even though DISTINCT is in the query, all the duplicates still show up.

1 --> SELECT DISTINCT (SCRNm)
FROM GBSp
ORDER BY ID DESC;

2 --> SELECT DISTINCT (SCRNm), GBSp.ID
FROM GBSp
ORDER BY GBSp.ID;

I have also tried putting ID in brackets, like [ID] in case of it being a reserved keyword.

I have tried putting table names along with the column names in case that was a reason, like: GBSp.SCRNm

I have tried just about everything I can think of. The only possible sources of error I can see are:

--ID is the primary key

--There are some values that are the same in the SCRNm field, but have different IDs along with.. I will set up an example below to show this.

SCRNm:two, ID: 1

SCRNm:three, ID: 2

SCRNm: eight, ID: 3

SCRNm: two, ID: 4

SCRNm: six, ID: 5

SCRNm: three, ID: 6

What the query should do to that data is output this:

three

eight

--SQL will not allow using a SELECT DISTINCT along with an ORDER BY

--Something else?

I have given myself too many headaches trying to figure this out... Can anyone else think of anything to try?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-02 : 09:25:37

select SCRNm
from GBsp
GROUP BY SCRNm
ORDER BY Min(ID) ASC

In your example data, you explain exactly WHY you can't order by a field different from what you are returning, using DISTINCT. In your data:

SCRNm:two, ID: 1
SCRNm:three, ID: 2
SCRNm: eight, ID: 3
SCRNm: two, ID: 4
SCRNm: six, ID: 5
SCRNm: three, ID: 6

What the query should do to that data is output this:

two
three
eight
six

WHY should the query return data that way? "two" has an ID of both 4 and 1. Why should it return "two" before "three", which has an ID of 2? It could go either way. No rule says that SQL should take the MIN of all ID's assigned to each SCRNm and use THAT to order by -- you need to explicity tell it to do so. That's what the SQL I posted for you does.

But even that's not enough. What about data like this:

SCRNm, ID
one, 1
one, 3
two, 1
two, 2

Which should be returned first, "one" or "two" ? the MIN() of the ID for both is 1, so that formula doesn't work as well. IN the case of a tie, what should you do?

I hope you can begin to see the complexities of all this. That is why SQL doesn't allow you to ORDER BY a field that is not in the DISTINCT list. I hope this makes a little sense...

- Jeff

Celtics3410
Starting Member

11 Posts

Posted - 2003-07-02 : 11:32:44

Jeff,

I will try that.

But if I get what you're saying here, it isn't exactly whats needed. In the actual data, there are over 1,000 items in the database, so making sure the DISTINCT function works is important.

You gave an example like this:

quote:

SCRNm, ID
one, 1
one, 3
two, 1
two, 2

But I would need something more like this:
one
two

I can't have duplicates because of the amount of records that will appear. The MIN idea sounds great, but I haven't tried it yet. Just to reiterate what I need the query for:

Distinct SCRNm records, NOT in alphabetical order, but ordered by the ID column.

Maybe I missed something you meant.. But will this example query you posted allow duplicates? If it does, I can't use it. I already have a query working with all the records. However, I would much rather have only one instance of each record showing up, while keeping the count of those records intact.

Edited by - celtics3410 on 07/02/2003 11:33:46

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-02 : 11:37:47

quote:
Distinct SCRNm records, NOT in alphabetical order, but ordered by the ID column.

Which one????

Given:
one, 1
one, 3
two, 1
two, 2

Would "one" come before or after "two"? And why?

Jonathan
{0}

Celtics3410
Starting Member

11 Posts

Posted - 2003-07-02 : 12:12:35

Sorry Jeff,

I'm definitely not a morning person. That seems to do exactly what I want. Its only a matter of fixing up the table in the ASP to make it work.

Thanks!!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-02 : 12:38:25

Glad it helped. Jonathan, thanks for the assist.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources