SQL data sorting

GreatSamps
GreatSamps used Ask the Experts™
on
Hi,

i have a simple table with 4 rows, 2 numeric, 2 varchar.

varchar1, varchar2, num1, num2

the data within them is kinda mixed up, and i need to sort it. for example

apples, pears, 10, 20
pears, apples, 15, 15

what i need to do is sort it so that apples and pears are both in the same collums, like this:

apples, pears, 10, 20
apples, pears 15, 15

the sorting of the varchar colums needs to be done alpabetically.

i have thought that i could do this by making the database relational by moving the varchar vales into another table, and then giving them numberic ID's to sort them, but im sure there is a much simpler way.

any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

How about this?

select case when field1 < field2 then field1 else field2 end,
          case when field1 < field2 then field2 else field1 end,
          case when field1 < field2 then field3 else field4 end,
          case when field1 < field2 then field4 else field3 end
from yourtable


But if you would do this multiple times with the same data, your suggestion would be more efficient.

/peter


Commented:
You have to normalize your database, no sense duplicate columns with different object on same row, or, worse, to switch randomly varchar1 and varchar2.
You have to add a column to specify your single row sense. Basket?
With a relational table you can then order by every column you want.
A relational table could be:

Basket | Type   | Number
-----------------------
1      | apples |  10
1      | pears  |  20
2      | apples |  15
2      | pears  |  15

Open in new window

Commented:
If you don't want to change datamodel, you might want to do like this instead.

select field1, field2, field3, field4 from yourtable where field1 <= field2
union all
select field2, field1, field4, field3 from yourtable where field1 > field2
order by field1, field2
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
hi,

i dont need to change the data model, both of these examples work, with the exception that i cant do a group by on them ... get two different problems.

Author

Commented:
i think the group by problem may be my understanding of how group by works.

i want to group together all instances where field 1 and field 2 are the same with a count as field 3 ...

i thought that

group by field1, field2

would do this, but i think im mistaken

Commented:
what is your error?

have you tried a subquery?

select
from (select field1, field2, field3, field4 from yourtable where field1 <= field2
union all
select field2, field1, field4, field3 from yourtable where field1 > field2
) a
group by field1, field2

Author

Commented:
error is that the results are only grouped by field 1.

tried that last example. it does not like the first from ... syntax error ...

Commented:
Like this?

select field1, count(field3) from yourtable where field1=field2 group by field1

Author

Commented:
no, thats not what i need.

i will try and clarify

in my example

varchar1 varchar2, numberic1

apples, pears, 15
pears, apples, 20

i want to see the following

apples, pears, 2

i.e. count of the number of occurances of the combination of apples and pears or pears and apples ...

make any more sense?

Commented:
Sorry, I seem to miss your latest post all the time. :-)

The sql should be

select field1, field2, sum(field3), sum(field4)
from (select field1, field2, field3, field4 from yourtable where field1 <= field2
union all
select field2, field1, field4, field3 from yourtable where field1 > field2
) a
group by field1, field2
Commented:
And I missed it again :-)

OK, hows this?

select field1, field2, count(*)
from (select field1, field2, field3, field4 from yourtable where field1 <= field2
union all
select field2, field1, field4, field3 from yourtable where field1 > field2
) a
group by field1, field2

Commented:
GreatSamps, if you post your query we can understand what do you want and what's the error.

Author

Commented:
solution works perfectly.

thanks for you help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial