SELECT DISTINCT ordering results

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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT DISTINCT ordering results

Author

Topic

vito1281
Starting Member

12 Posts

Posted - 2004-06-05 : 22:23:20

Is there a way to prevent a SELECT DISTINCT query from ordering results alphabetically?

For example, suppose that my results WITHOUT using DISTINCT look like this:

B 20
A 10
B 40
C 20
D 30
C 10
A 30

I want to return just the distinct values from the first column, and the result set should look like this:

B
A
C
D

I can do this with a cursor, but simply doing SELECT DISTINCT... will return

A
B
C
D

Is there a way to return the items distinctly in the order that they appear in the original resultset w/out a cursor?

Thanks

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-06 : 00:45:04

You will get a lot of grief from relational DB theorists telling you there is no such thing as "the order of the original resultset"

The order in which data is returned depends on many things, including table size, generated query plans, cache, CPU count, etc.

With that said, the query below is one way to achive it, but it's a half-assed solution.

-- Create a temp table
CREATE TABLE #temp (c char, id int, orderby int identity(1,1))
--Add a unique index on the c column. Only the first selected value of c will be stored in the table
CREATE UNIQUE INDEX cidx on #temp (c) WITH IGNORE_DUP_KEY
--Insert rows. The identity column will increment giving an value to order by. This DEPENDS on data being inserted in the order you consider 'original'
INSERT #temp SELECT c, id FROM mytable

SELECT * FROM #temp order by orderby

DROP TABLE #temp

Kristen
Test

22859 Posts

Posted - 2004-06-06 : 07:31:35

Assuming you had some "sequence" identification in the original table you could do:


SELECT [Sequence]=1, C='B', N=20
INTO #TEMP 
UNION ALL SELECT 2, 'A', 10 
UNION ALL SELECT 3, 'B', 40 
UNION ALL SELECT 4, 'C', 20 
UNION ALL SELECT 5, 'D', 30 
UNION ALL SELECT 6, 'C', 10 
UNION ALL SELECT 7, 'A', 30

SELECT	C, 
	[MinSeq] = MIN(Sequence)
FROM	#TEMP
GROUP BY C
ORDER BY MinSeq, C
GO

-- To avoid displaying the MinSeq column:
SELECT	C
FROM
(
	SELECT	C, 
		[MinSeq] = MIN(Sequence)
	FROM	#TEMP
	GROUP BY C
) X
ORDER BY MinSeq, C
GO


DROP TABLE #TEMP
GO

Kristen

vito1281
Starting Member

12 Posts

Posted - 2004-06-06 : 14:11:21

Kristen, thanks for the suggestion. One thing that I failed to mention in my first post is that a given character (C) can appear more than once in the list AND have the same sequence number. I think your code assumes (and rightfully so, since I didn't elaborate on this) that a given character will have only one instance of it with the lowest sequence.

kselvia, I like your solution. The reason why I need a sense of order in this scenario is because that list of characters is used to build a view. Each of those characters represents a column for the view, and the order of these columns is important. The business scenario is this:

There are tests. Each test can have multiple subtests. Each subtest can have multiple score categories. For example, consider the SAT test. It has the Verbal and Math subtests. Verbal has score categories (analogies, reading comp, essay, etc) and Math has its score categories. In my situation, each test has a sequence, each subtest has a sequence, and each score category has a sequence.

In the view, I need to present all of the score category scores for each subtest of each test. The order of the score categories is determined by the sequence number. But the score categories table has the same category (e.g. "ESS") that appears for multiple subtests, and can have the same sequence # (e.g. ESS has sequence 10 for subtest 1 and has sequence 10 for subtest 2). To circumvent this issue, I join the score category table onto the subtest table and the test table. I then sort the join by test.sequence, subtest.sequence, and scorecategory.sequence. Once I get that join, I use a "first-come first-serve" approach to determine the order. In other words, if C appears first in the result set, C becomes first in the order. If B is next, B will be second, and so forth.

This is why the concept of order is important here because the sequence #s overlap, and so will not give me the correct order.

I guess I could also use a while loop to examine the rows, but that would be just like a cursor anyways.

If anyone has any more feedback, I'd greatly appreciate it.

Thanks

Kristen
Test

22859 Posts

Posted - 2004-06-07 : 01:27:21

quote:
Originally posted by vito1281

Kristen, thanks for the suggestion. One thing that I failed to mention in my first post is that a given character (C) can appear more than once in the list AND have the same sequence number. I think your code assumes (and rightfully so, since I didn't elaborate on this) that a given character will have only one instance of it with the lowest sequence.

No, I didn't assume the sequence, I added it to your data!

Your 10, 20, 30 "value" column looked like duplicates, so I added a 1,2,3 "sequence" column at the front.

SQL has no concept of what order the data is in - you will ahve to have a column to tell you that - for exmaple, the date/time when the record was inserted (if chronological order is what you need), otherwise you need an IDENTITY column to auto-number the records in the order they are inserted - or something else :-)

If you do SELECT * FROM MyTable today, and then you do that again tomorrow the records may NOT be in the same order tomorrow (SQL is under no obligation to give them to you in any particular order, and database maintenance can change the order that SQL processes them). To do that you have to use an ORDER BY to force the order.

Given such a "sequence" column my script-ette will give you one entry for your A,B,C column, and they will be in the order of lowest "sequence" column coming first.

Dunno if that helps, or solves the problem you have though.

Kristen

vito1281
Starting Member

12 Posts

Posted - 2004-06-07 : 09:19:08

I see what you're saying now. I think I misinterpreted what the "sequence" column in your example was. I thought it was the 10, 20, 30 values, but it was the auto-numbered values--I should've paid closer attention! :)

I didn't know that running SELECT * from myTable could produce a different output from time to time, even if the data didn't change--that's interesting!

Anyhow, thanks for the help.

quote:
Originally posted by Kristen

quote:
Originally posted by vito1281

Kristen, thanks for the suggestion. One thing that I failed to mention in my first post is that a given character (C) can appear more than once in the list AND have the same sequence number. I think your code assumes (and rightfully so, since I didn't elaborate on this) that a given character will have only one instance of it with the lowest sequence.

No, I didn't assume the sequence, I added it to your data!

Your 10, 20, 30 "value" column looked like duplicates, so I added a 1,2,3 "sequence" column at the front.

SQL has no concept of what order the data is in - you will ahve to have a column to tell you that - for exmaple, the date/time when the record was inserted (if chronological order is what you need), otherwise you need an IDENTITY column to auto-number the records in the order they are inserted - or something else :-)

If you do SELECT * FROM MyTable today, and then you do that again tomorrow the records may NOT be in the same order tomorrow (SQL is under no obligation to give them to you in any particular order, and database maintenance can change the order that SQL processes them). To do that you have to use an ORDER BY to force the order.

Given such a "sequence" column my script-ette will give you one entry for your A,B,C column, and they will be in the order of lowest "sequence" column coming first.

Dunno if that helps, or solves the problem you have though.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources