CODEWITHSUNDEEP
sqlsqlite
Squazic
Squazic

Reputation: 3690

How do you order by column within a row in sql?

Say I have a table that looks something like this

ATT A | ATT B
-------------
A     | B
D     | C
E     | F
H     | G

and instead I want a table that looks like:

ATT A | ATT B
-------------
A     | B
C     | D
E     | F
G     | H

How would I go about doing that? I'm using SQLite.

Upvotes: 0

Views: 178

Answers (2)

James L.
James L.

Reputation: 9451

SQL Server has a case .. end expression that you can use inline in the SQL statement. I think SQLite uses the same syntax. This does what you asked for in SQL Server:

create table #temp (AttA char(1), AttB char(1))

insert into #temp valueS ('A', 'B'), ('D', 'C'), ('E', 'F'), ('H', 'G')

select * from #temp

select case when AttA < AttB then AttA else AttB end as AttA,
       case when AttB > AttA then AttB else AttA end as AttB
from   #temp

drop table #temp

Upvotes: 2

Didier Spezia
Didier Spezia

Reputation: 73246

Supposing the columns have the same type and you just have 2 columns to reorder, you can rely on the min/max functions. With sqlite, when provided with multiple parameters, these functions do not aggregate data from multiple rows.

create table mytable( ATTA char, ATTB char );
insert into mytable values ('A', 'B');
insert into mytable values ('D', 'C');
insert into mytable values ('E', 'F');
insert into mytable values ('H', 'G') ;

select min(ATTA,ATTB),max(ATTA,ATTB) from mytable order by 1,2 ;
A|B
C|D
E|F
G|H

The min/max functions reorder the values in the columns. The order by clause reorders the rows. I think it cannot be generalized to more than 2 columns, except by writing a user defined C function to be called from sqlite.

Upvotes: 1

Related Questions