How do I select distinct values from a table without order by

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I have a below scenario in sql server. Please help me in this

SQL

SELECT ListItem as ColumnName from (
SELECT REPLACE(ColumnsList, '$', ListItem) ListItem FROM dbo.MODELS_COLUMNS_STRUCTURE MCS
CROSS JOIN List_Resources WHERE Type = 'country data' ) A

For this query I'm getting result as

VB

Country
Country
Country
Region
Region
Region
Currency
Currency
Currency
Allocation
Allocation
Allocation
Business
Business
Business
CurrencyUSDRate
CurrencyUSDRate
CurrencyUSDRate
CurrencyAUDRate
CurrencyAUDRate
CurrencyAUDRate
CurrencyEURRate
CurrencyEURRate
CurrencyEURRate

I want output as

VB

Country
Region
Currency
Allocation
Business
CurrencyUSDRate
CurrencyAUDRate
CurrencyEURRate

Thanks & Regards,
Mathi.

Posted 18-Feb-14 2:44am

Mathi2code

Add a Solution

Comments

The14thNoah 19-Feb-14 1:19am

why all the suggested solutions were same? . .are they copying the solution of other's answer?

Andrius Leonavicius 19-Feb-14 14:55pm

I think they all copied questioner's SELECT statement and added keyword DISTINCT. :)

The14thNoah 19-Feb-14 18:45pm

hahahaha even they see all the previous answer they still post the same answer? :)

Andrius Leonavicius 19-Feb-14 19:19pm

No, I don't think so. :) I can't speek for them, but I can tell something from my experience. Once I saw some easy question, which was posted like 5 minutes ago (0 solutions). Then I started writing a solution. After I posted it, my solution was 4th. Why? Because that data wasn't automatically refreshed while I was writing and others posted their solutions. Probably the same thing here...

The14thNoah 19-Feb-14 19:32pm

ok. . .but you can see the time interval when they post it.Im not arguing for something,Im just find it funny. . .thanks for the reply :)

3 solutions

Solution 2

why don't you use

SQL

SELECT DISTINCT ListItem as ColumnName from (
SELECT REPLACE(ColumnsList, '$', ListItem) ListItem FROM dbo.MODELS_COLUMNS_STRUCTURE MCS
CROSS JOIN List_Resources WHERE Type = 'country data' ) A

Posted 18-Feb-14 2:52am

MalikRizwan

Comments

Mathi2code 19-Feb-14 0:52am

If I use distinct, it automatically does order by asc, and the result will be like below.

Allocation
Business
Country
Currency
CurrencyAUDRate
CurrencyEURRate
CurrencyUSDRate
Region

but I want in the same order as mentioned in the question. Is this possible.

Solution 3

Use the below query

SQL

SELECT DISTINCT  ListItem as ColumnName from (
SELECT REPLACE(ColumnsList, '$', ListItem) ListItem FROM dbo.MODELS_COLUMNS_STRUCTURE MCS
CROSS JOIN List_Resources WHERE Type = 'country data' ) A

Posted 18-Feb-14 3:25am

Gauri Chodanker

Comments

Mathi2code 19-Feb-14 0:52am

If I use distinct, it automatically does order by asc, and the result will be like below.

Allocation
Business
Country
Currency
CurrencyAUDRate
CurrencyEURRate
CurrencyUSDRate
Region

but I want in the same order as mentioned in the question. Is this possible.

Gauri Chodanker 20-Feb-14 7:46am

Can you please provide me sample data and table name. I shall write the query for that.

Does your table MODELS_COLUMNS_STRUCTURE use some number field like primarykey. And what is the order in which your data is stored. Is it country first and then allocation or the order in which you want the data. If that is so then use the below query

SELECT DISTINCT somenumberfield, ListItem as ColumnName from (
SELECT REPLACE(ColumnsList, '$', ListItem) ListItem FROM dbo.MODELS_COLUMNS_STRUCTURE MCS
CROSS JOIN List_Resources WHERE Type = 'country data' ) A

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)

OriginalGriff · Accepted Answer · 2014-02-18T02:50:00

Solution 1

Try:

SQL

SELECT DISTINCT ListItem as ColumnName from (
SELECT REPLACE(ColumnsList, '$', ListItem) ListItem FROM dbo.MODELS_COLUMNS_STRUCTURE MCS
CROSS JOIN List_Resources WHERE Type = 'country data' ) A

Posted 18-Feb-14 2:50am

OriginalGriff

Comments

Mathi2code 19-Feb-14 0:50am

If I use distinct, it automatically does order by asc, and the result will be like below.

Allocation
Business
Country
Currency
CurrencyAUDRate
CurrencyEURRate
CurrencyUSDRate
Region

but I want in the same order as mentioned in the question. Is this possible.

OriginalGriff 19-Feb-14 4:39am

Not necessarily. The problem is that unless you specify an order with an ORDER BY clause, then SQL is at liberty to return rows in whatever order is best for it: which in the case of a DISTINCT query is sorted, because that is the easiest way to work out the DISTINCT requirement: you sort the inputs, then the duplicates are next to each other and easy to discard.
Unless your table includes some information which allows you to order the rows specifically, you can't enforce any particular order on SQL output - even if you don't use DISTINCT than this applies!

Mathi2code 19-Feb-14 8:17am

Ok :( Thanks.

Regards,
Mathi.

OriginalGriff 19-Feb-14 9:32am

You're welcome!

Mathi2code 20-Feb-14 6:17am

Thanks I've added an ID column to get it order by ID as suggested.

OriginalGriff 20-Feb-14 6:20am

:thumbsup: