sql Distinct is changing the order of row in table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

When i am using distinct it is always bringing the null value on top.Means order of the row is getting changed please help me out.

How to avoid this

NOTE- i can not use order By clause

Posted 6-Feb-13 20:07pm

Himanshu Yadav

Add a Solution

Comments

Ankur^\m/ 7-Feb-13 2:18am

Filter the null value in the where clause if you don't want it.
And why can't you use 'Order By' clause?

Himanshu Yadav 7-Feb-13 2:26am

original order of row
dddd
ffff
gg
null
ss
null

after using DISTINCT

null
dddd
ffff
gg
ss

row order is changed...

Chris Reynolds (UK) 7-Feb-13 9:21am

I think we need to know why you can't use ORDER BY as this is the only way that you can be sure what order your result set will appear in.

RedDk 7-Feb-13 14:14pm

What he's intimating is that the duplicate has been eliminated because he's used DISTINCT however the fact that it's NULL means that he wants to position that NULL between gg and ss.

Chris Reynolds (UK) 8-Feb-13 4:02am

If what he's saying is that he wants null (and I assume that this is a database null rather than the literal) to be treated as starting with the letter n and being sorted as though it were the literal 'null' then he could cheat and do something like:

SELECT Col1 from Table order by ISNULL(Col1, 'null')

which would fool the sort order.

Himanshu Yadav 12-Feb-13 0:32am

ys u r correct..

2 solutions

Solution 1

I don't see "the order of the row is getting changed ..."
[edit]
(I mean, not technically, because that NULL on the top of the stack is eliminated)
[end edit]

--CREATE SCHEMA [cpqa]
USE [cpqaAnswers]
GO
CREATE TABLE  [cpqaAnswers].[cpqa].[tblHY](
	[etches][nvarchar](48)
	)
	
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)
	VALUES('dddd') 	
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)
	VALUES('ffff')
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)	
	VALUES('gg')
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)	
	VALUES(NULL) 	
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)
	VALUES('ss')
INSERT INTO [cpqaAnswers].[cpqa].[tblHY](etches)
	VALUES(NULL)
	
SELECT [etches] FROM [cpqaAnswers].[cpqa].[tblHY]

SELECT DISTINCT [etches] FROM [cpqaAnswers].[cpqa].[tblHY]

SELECT * FROM [cpqaAnswers].[cpqa].[tblHY] WHERE [etches] != 'NULL'

That last SELECT clause' results:

etches
~~~~~~
dddd
ffff
gg
ss

Posted 8-Feb-13 6:38am

RedDk

Updated 11-Feb-13 18:37pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Himanshu Yadav · Accepted Answer · 2013-03-06T17:33:00

Hi i got the solution it will be like this..Thanks all to help me

SQL

WITH CTE AS (
SELECT SUBSTRING(code,2,2) as c_code, ProdName, ROW_NUMBER() OVER(PARTITION BY SUBSTRING(code,2,2)ORDER BY SERIALNUMBER,PRODUCTNUMBER,SOURCE) AS RowNumber
FROM Productdetail
)
SELECT * INTO #ProdDetail FROM CTE WHERE RowNumber =1

This will not change the order but gives the record on the top that what i was needed