SQL multiple column ordering
ORDER BY column1 DESC, column2
This sorts everything by column1 (descending) first, and then by column2 (ascending, which is the default) whenever the column1 fields for two or more rows are equal.
How to order by multiple columns in sql
this is the order by you need:
order by type desc,
id desc,
case when isdate(last_updated_date) then last_updated_date else 0 end desc
use asc and desc modifiers, for each order-by column you need
Order by multiple columns in SQL Server
You can use CASE EXPRESSION for conditional ordering:
SELECT * FROM Table
ORDER BY CASE WHEN @SortOrder = 1 then c1
WHEN @SortOrder = 2 then c2
ELSE c3
END,
CASE WHEN @SortOrder = 1 then c2
ELSE c1
END,
CASE WHEN @SortOrder in(1,2) then c3
ELSE c2
END
SQL ORDER BY multiple columns
Sorting in an ORDER BY is done by the first column, and then by each additional column in the specified statement.
For instance, consider the following data:
Column1 Column2
======= =======
1 Smith
2 Jones
1 Anderson
3 Andrews
The query
SELECT Column1, Column2 FROM thedata ORDER BY Column1, Column2
would first sort by all of the values in Column1
and then sort the columns by Column2 to produce this:
Column1 Column2
======= =======
1 Anderson
1 Smith
2 Jones
3 Andrews
In other words, the data is first sorted in Column1 order, and then each subset (Column1 rows that have 1 as their value) are sorted in order of the second column.
The difference between the two statements you posted is that the rows in the first one would be sorted first by prod_price (price order, from lowest to highest), and then by order of name (meaning that if two items have the same price, the one with the lower alpha value for name would be listed first), while the second would sort in name order only (meaning that prices would appear in order based on the prod_name without regard for price).
Order by multiple column first with alphabet then numbers
Ordering is determined by collations in SQL Server and DB2. It seems your iSeries DB2 is configured with an EBCDIC collation so you could add an explict COLLATE clause to the ORDER BY expression to coerce EBCDIC ordering rules for SQL Server since your SQL Server collation is apparently different.
Below is an example of your original DB2 query with the clause added for the SQL Server:
ORDER BY
CASE WHEN LEN(RTRIM(LTRIM(CorpName))) > 1 THEN CorpVal Else '999' END COLLATE SQL_EBCDIC037_CP1_CS_AS,
CASE WHEN SUBSTRING(FName,1,1) != '*' THEN FName Else '999' END COLLATE SQL_EBCDIC037_CP1_CS_AS
How to order by the sum of multiple columns
You would add another column:
SELECT MID.menu_item_id,
SUM(CID.consumed_quantity) as total
SUM(CASE WHEN CSD.tendered_date_time BETWEEN
CONVERT(datetime,'2016-07-01 06:00:00.000') AND CONVERT(datetime, '2016-08-01
05:59:59.999') THEN CID.consumed_quantity ELSE 0 END) AS [JUL],
. . .
. . .
ORDER BY SUM(CID.consumed_quantity) DESC;
Oracle SQL order by doesn't order multiple columns
Here is some sample input data, which I hope will make the following explanation clearer:
id | first_name | last_name | street_address
10 | Albert | Camus | 300 Carroll St
20 | Caroline | Aherne | 200 Carroll St
30 | Bertoldt | Brecht | 500 Carroll St
40 | Albert | Dumbledore | 400 Carroll St
50 | Bertoldt | Brecht | 100 Carroll St
Here we have five records, which tell us useful things, like Albert Camus lives at 300 Carroll Street and Caroline Ahern lives at 200 Carroll St. The important thing to understand about a SQL query is that it retrieves records; when we use ORDER BY it sorts the records, not the individual columns.
So when we sort these records ordering by first_name, last_name, street_address we get this result:
id | first_name | last_name | street_address
10 | Albert | Camus | 300 Carroll St
40 | Albert | Dumbledore | 400 Carroll St
50 | Bertoldt | Brecht | 100 Carroll St
30 | Bertoldt | Brecht | 500 Carroll St
20 | Caroline | Aherne | 200 Carroll St
The result set is the table's records sorted into first_name order; when two records have the same value first_name the sort uses last_name to break the tie; when both first_name and last_name are the same the sort uses street_address. This is exactly what we would expect. The records remain intact.
What would it mean to sort the columns independently? There is no such person as Albert Aherne living at 100 Carroll Street. So how would SQL make one up?
If you still quite can't get the way this works, notice that I added a surrogate primary key to the table. The ID column uniquely identifies each record. So ID = 30 identifies the Bertoldt Brecht who lives at 500 Carroll St. Supposing ORDER BY worked the way you expect: what would the value of ID be for Albert Aherne living at 100 Carroll Street?
According to the normalization forms, in a table, all records must be related to the primary key and it means you can't sort multiple columns then why we need sorting multiple columns?
Normalisation has nothing to do with it. We might want to order by columns of a compound key or even by non-key attributes. In such circumstances we often need to order by multiple columns.
For instance, I shuffle a deck of cards. Now I ask you to take five cards and put them in ascending order, aces low. Easy enough. Except that you drew the seven of spades and the seven of hearts. Which comes first? Spades is higher than hearts, so seven of hearts then seven of spades. But both before nine of clubs.
order by multiple columns
Get the leader of each group and sort them by descending time:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
)
select *
from grp
order by TimeGroup desc;
Output:
NAME IMPFILE TIMEGROUP IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Then join the followers to leader and get the leader's time(TimeGroup):
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc, Name
Output:
NAME IMPFILE IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Imp01 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
The logic of the query is, we align the followers(those with name)'s time to their leader's time(TimeGroup) based on ImpFile. Leader and its followers have same time group, so when we sort them by time, they will stick to each other; then after that, we sort by name
Live test: http://www.sqlfiddle.com/#!3/c7859/21
If we want the group leader to appear after its followers, just put a case when on ORDER BY:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc,
case
when Name is null then 2 -- leader last
else 1 -- followers first
end,
Name
Output:
NAME IMPFILE IMPTIME
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Import12 2012-05-16 09:55:37.3870000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
(null) Imp01 2012-05-16 09:54:02.4780000
Live test: http://www.sqlfiddle.com/#!3/c7859/23
How it works:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select *
from grp
order by TimeGroup desc, Name;
Output:
NAME IMPFILE IMPTIME TIMEGROUP
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000 2012-05-16 09:55:37.3870000
Sasha Import12 2012-05-16 09:55:37.3850000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
Sam Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
Live test: http://www.sqlfiddle.com/#!3/c7859/25
Related Topics
How to Insert a Value That Contains an Apostrophe (Single Quote)
Grouping into Interval of 5 Minutes Within a Time Range
Sqlite Insert - on Duplicate Key Update (Upsert)
Foreign Key to Non-Primary Key
Multiple Insert Statements Vs. Single Insert With Multiple Values
Is the Nolock (SQL Server Hint) Bad Practice
Ora-12560: Tns:Protocol Adaptor Error
MySQL Query to Dynamically Convert Rows to Columns
How to Make a Row Generator in MySQL
Select Rows Which Are Not Present in Other Table
How to Insert Multiple Rows At a Time in an Sqlite Database
SQL Query to Concatenate Column Values from Multiple Rows in Oracle
Optimize Group by Query to Retrieve Latest Row Per User
MySQL How to Fill Missing Dates in Range
Left Outer Join Doesn't Return All Rows from My Left Table