CODEWITHSUNDEEP
sqlsql-order-byconcatenation
DEwok
DEwok

Reputation: 73

SQL Order By using concat

I'm concatenating two fields and I only want to order by the second field (p.organizationname). Is that possible?

I'm displaying this field so I need a solution that doesn't include me having to select the fields separately.

Here is what i have so far:

SELECT distinct Concat(Concat(f.REFERENCEFILE, ','),p.ORGANIZATIONNAME)
FROM PEOPLE p,FOLDER f,FOLDERPEOPLE fp,folderinfo fi...
Order By concat(Concat(f.REFERENCEFILE, ','),p.ORGANIZATIONNAME)

Upvotes: 0

Views: 18583

Answers (2)

Hart CO
Hart CO

Reputation: 34784

Use GROUP BY and ORDER BY an aggregate instead of DISTINCT:

SELECT Concat(Concat(f.REFERENCEFILE, ','),p.ORGANIZATIONNAME)
FROM PEOPLE p,FOLDER f,FOLDERPEOPLE fp,folderinfo fi...
GROUP BY Concat(Concat(f.REFERENCEFILE, ','),p.ORGANIZATIONNAME)
Order By MAX(p.ORGANIZATIONNAME)

The problem can be illustrated with an example:

ID Col1
1  Dog
1  Cat
2  Horse

Distinct ID? Easy: 1,2

Distinct ID Order by Col1... wait.. which value of Col1 should SQL use? SQL is confused and angry.

Since you are using a concatenation of two fields and want to sort by one of those fields, you could also include the sort field in a DISTINCT subquery and then ORDER BY the sort field without including it in your SELECT list.

Upvotes: 4

mcha
mcha

Reputation: 2998

Since you have a DISTINCT your ORDER BY clause should be specified in the SELECT, you can use a subquery to achieve the same result in your case since the Distinct values will be the same when you add P.ORGANIZATIONNAME

SELECT col 
  FROM( SELECT distinct Concat(Concat(f.REFERENCEFILE, ','),p.ORGANIZATIONNAME) a,
               p.ORGANIZATIONNAME b
          FROM PEOPLE p,FOLDER f,FOLDERPEOPLE fp,folderinfo fi... ) t
   order by b

Upvotes: 1

Related Questions