CODEWITHSUNDEEP
sqlsql-server
FarSoft Group
FarSoft Group

Reputation: 59

Order by columns in sql

I have a table with these data:

hour1 | hour2 | hour3 | date
h1      h2      h3      d1
h4      h5      h6      d2

Regardless of the values (without using order by) I want to use a query to show them with this order:

h_value | date
h1        d1
h2        d1
h3        d1
h4        d2
h5        d2
h6        d2

I have tried many queries and statements like union , join, etc ... but none of them works. The last code that I have tried:

SELECT test.h1 as h_value,
        test.h2 as h_value,
        test.h3 as h_value,
        test.date
      FROM test

The result of this code is: 

h_value| h_value| h_value| date
h1       h2       h3       d1
h4       h5       h6       d2

Any solution? (Notice: Ignore table data, Only in the order of the specified columns:

h_value | date
row1col1  date1
row1col2  date1
row1col3  date1
row2col1  date2
row2col2  date2
row2col3  date2

I hope you know what I mean)

EDIT 1 look, I can't use order by or use where because both of them are tested and result was false, if my data on table is like this:

hour1 | hour2 | hour3 | date
5:00    7:00    4:00    2018/10/09
3:30    8:00    4:00    2018/09/20
7:00    9:00    6:00    2018/11/10

How do I suppose to order this data as I want? Its a simple question to understand!

Upvotes: 0

Views: 55

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

For SQL Sever you coudl use unpivot llike this:

SELECT h_value, adate
FROM   
   (SELECT h1,h2,h3,adate
   FROM test) p  
UNPIVOT  
   (h_value FOR src IN   
      (h1,h2,h3)  
)AS unpvt
order by adate, h_value;

But you retain the issue that SQL requires the use of order by to guarantee an ordered result.

original suggestion (when db was listd as MySQL)

SELECT
    test.h1 AS h_value, test.date
FROM test
UNION ALL
SELECT
    test.h2 AS h_value, test.date
FROM test
UNION ALL
SELECT
    test.h3 AS h_value, test.date
FROM test
ORDER BY
    h_value, date

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I have a strong preference for using cross apply instead of unpivot for unpivoting. Basically, apply is very powerful and can do many things. This is a good way to learn about the functionality.

However, if you want the results in a particular order, you have to use order by. SQL tables and result sets represent unordered sets. There is no ordering, unless you explicitly insist.

So, I would go for:

select s.date, h.h_value
from student s cross apply
     (values (hour1, 1), (hour2, 2), (hour3, 3)
     ) h(hour, ord)
order by s.date, h.ord;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

USE Unpivot in case of sql server:

    select date, hvalue
from student
unpivot
(
  hvalue
  for val in (hour1, hour2, hour3)
) u

http://sqlfiddle.com/#!18/2e0d2/2

Upvotes: 2

Related Questions