Distinct with no order

Question

Distinct with no order

Charbel-371270

SSCrazy

Points: 2260
More actions
March 26, 2007 at 1:54 am

#172593

i have a table lets say table1(id,name)
i need to
select distinct id from table1
but the distinct is returning the results by order
i dont want the order , i want to retrive the ids with no order
ex: lets say the data is like this :   3     three
                                                 1     one
                                                 1      onee
                                                  2      two
the select distinct id from table1 will return : 1 ,2,3
i need it to return : 3,1,2
so how can i perform distinct with no ordering?
i solved it in many ways im just wondering if i thers like a predefined Tsql keyword .... that let me do this

thanks
If something's hard to do, then it's not worth doing.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'no order'?

If you want your data back in a specific order, you must specify an order by clause. Otherwise it comes back however the rows were ordered after SQL finished all the query operations.

To take a distinct set, SQL must order the rows, so that it can find and remove the duplicates. How it does that is dependent on the query, the server's state, the amount of data and a number of other things.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 2

i meant that the result are ordered after the distinct

i want o get the results as they are in the table

check the example i provided

If something's hard to do, then it's not worth doing.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

The order of the rows in a table has no meaning in a relational database system. Does your table have a primary key constraint? If so, you can order by that. Perhaps you have a datetime column that shows when the row was inserted into the table? That may also be a good column to order by.

John

ganeshc27 SSC Eights! Points: 922 More actions · Answer 4

you've mentioned that you've solved it many ways. Can you explain how did you do that ?

Regards,

Ganesh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I saw your example. Why 3,1,2. why not 2,1,3?

As I said, if you want to have data in a specific order (whatever it may be) you have to specify an order by. Tables and resultsets from a query without order by are considered unordered sets. ie, the order is however the query execution engine leaves it.

You can't say you want the data in the order it is in the table, because tables are collections of rows without an order.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 6

obviously there is an implied order somewhere, otherwise you would not want them in 3,1,2 order...is there a datetime field, or the order of insert (based on an identity field?) that you want?

whatever that field is, you want to add ORDER BY [THATFIELD] to get what you are after.

you can also use a CASE statement for really unusualy requirements, like

ORDER BY CASE WHEN somefiled = 'three' THEN 0 WHEN somefiled = 'one' THEN 2 ELSE 3 END

but it seems to me you have an "order by" you want in your data, but just just haven't identified it and sued it in the query.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 7

Hi ,

Try using this query i think it might solve ur problem.........

select distinct empid , newid() from dbo.Employee

order by newid()

Regards ,

Amit Gupta

syi916 SSC Enthusiast Points: 115 More actions · Answer 8

newid() only randomizes the order.. is that what you want?

how the data is stored in the database is meaningless unless you have an id column or a insert_date column. i'm not sure what you're trying to achieve here.

Marino van der Heijden SSC-Addicted Points: 412 More actions · Answer 9

@amit-2 Gupta: In your example all results will become unique because of a newid() added to your select. Therefore the DISTINCT will not work anymore.

@Charbel: this is never gonna happen unless you loop through your results and filter all doubles yourself! But that's probably not what you want to do.

What we all are wondering is: why do you want to use this 'no order' result??? It makes no sense to us, because not defining an order says that it doesn't matter in what order you get your results.

Now you probably get your results ordered by the moment that a row was added (first added row first in result). But that's just the way SQL Server orders this internally and you have no influence on that. An added index on your table might result in a different internally ordered result set for example...

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 10

@Charbel: this is never gonna happen unless you loop through your results and filter all doubles yourself! But that's probably not what you want to do.

that wat i did and i want tp avoid it

anyway 10x guys

If something's hard to do, then it's not worth doing.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 11

Use a GROUP BY instead of distinct. Then you can avoid duplicates with something like :

SELECT ID, [Name], MIN(SequenceField) FROM dbo.MyTable GROUP BY ID, [Name]

... but, as everyone says, you MUST have a sequence field of some kind. There is no such thing as a 'natural' or 'physical' order in a relational database like SQL Server.