order by multiple columns

问题:

I need to order data by two columns, How do i do that?

This is my table:

Name   |  ImpFile   |  ImpTime
Sam      Imp01        2012-05-16 09:54:02.477
Ann      Imp01        2012-05-16 09:54:02.478
Mark     Imp01        2012-05-16 09:54:02.477
John     Import12     2012-05-16 09:55:37.384
Bart     Import12     2012-05-16 09:55:37.387
Sasha    Import12     2012-05-16 09:55:37.385

I need to sort this table by ImpTime and ImpName and it should look like this:

Name   |  ImpFile   |  ImpTime
         Import12    2012-05-16 09:55:37.387
Bart     Import12    2012-05-16 09:55:37.387
John     Import12    2012-05-16 09:55:37.384
Sasha    Import12    2012-05-16 09:55:37.385
         Imp01       2012-05-16 09:54:02.478
Ann      Imp01       2012-05-16 09:54:02.478
Mark     Imp01       2012-05-16 09:54:02.477
Sam      Imp01       2012-05-16 09:54:02.477

I am using this query, but it does not order the table by Name, in only orders by name when the time is the same value for multiple rows.

select Name, ImpFile, ImpTime
from people
union
select distinct '', ImpFile, max(ImpTime)
from people 
group by ImpFile
order by ImpTime desc, Name

This query gives me table like this:

Name   |  ImpFile   |  ImpTime
         Import12     2012-05-16 09:55:37.387
John     Import12     2012-05-16 09:55:37.384
Bart     Import12     2012-05-16 09:55:37.387
Sasha    Import12     2012-05-16 09:55:37.385
         Imp01        2012-05-16 09:54:02.478
Sam      Imp01        2012-05-16 09:54:02.477
Ann      Imp01        2012-05-16 09:54:02.478
Mark     Imp01        2012-05-16 09:54:02.477

Is there any way to order by those two column at the same time?

EDIT What happens when I use order by ImpFile DESC, ImpTime desc?
It gives me a result table like this:

Name   |  ImpFile   |  ImpTime
         Import12    2012-05-16 09:55:37.387
         Imp01       2012-05-16 09:54:02.478
Bart     Import12    2012-05-16 09:55:37.387
John     Import12    2012-05-16 09:55:37.384
Sasha    Import12    2012-05-16 09:55:37.385
Ann      Imp01       2012-05-16 09:54:02.478
Mark     Imp01       2012-05-16 09:54:02.477
Sam      Imp01       2012-05-16 09:54:02.477

回答1:

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

回答2:

Why can you just do it like this:

order by ImpFile DESC, ImpTime desc

No it do not result in what you are showing. It result in this:

        Import12    2012-05-16 09:55:37.387
Bart    Import12    2012-05-16 09:55:37.387
Sasha   Import12    2012-05-16 09:55:37.387
John    Import12    2012-05-16 09:55:37.383
        Imp01       2012-05-16 09:54:02.477
Ann     Imp01       2012-05-16 09:54:02.477
Mark    Imp01       2012-05-16 09:54:02.477
Sam     Imp01       2012-05-16 09:54:02.477

See here for an example

EDIT

I have a suggestion for you. Maybe something like this:

Test data

DECLARE @T TABLE(Name VARCHAR(100),ImpFile VARCHAR(100),ImpTime DATETIME)

INSERT INTO @T
    ([Name], [ImpFile], [ImpTime])
VALUES
    ('Sam', 'Imp01', '2012-05-16 09:54:02.477'),
    ('Ann', 'Imp01', '2012-05-16 09:54:02.478'),
    ('Mark', 'Imp01', '2012-05-16 09:54:02.477'),
    ('John', 'Import12', '2012-05-16 09:55:37.384'),
    ('Bart', 'Import12', '2012-05-16 09:55:37.387'),
    ('Sasha', 'Import12', '2012-05-16 09:55:37.385');

Query

;WITH CTE
AS
(   
    SELECT
        ROW_Number() OVER(PARTITION BY  t.[ImpFile] 
                     ORDER BY t.[ImpTime] DESC) AS RowNbr,
        '' AS Name,
        t.ImpFile,
        t.[ImpTime]
    FROM
        @T AS t
)
SELECT
    CTE.Name,
    CTE.ImpFile,
    CTE.[ImpTime],
    0 as SortOrder
FROM
    CTE
WHERE
    CTE.RowNbr=1
UNION ALL
SELECT
    t.Name,
    t.ImpFile,
    t.[ImpTime],
    1 as SortOrder
FROM
    @T AS t
ORDER BY
    ImpFile DESC,SortOrder, ImpTime desc

回答3:

First of all, you should realise that with the datetime type's granularity, SQL Server is unable to distinguish between 2012-05-16 09:55:37.384 and 2012-05-16 09:55:37.385: both would be stored as 2012-05-16 09:55:37.384.

Bearing that in mind and assuming that you want to sort the groups by MAX(ImpTime) DESC as well as the detail rows by ImpTime DESC, you could try something like this:

WITH agg AS (
  SELECT
    *,
    ImpTimeMax = MAX(ImpTime) OVER (PARTITION BY ImpFile),
    rn         = ROW_NUMBER() OVER (PARTITION BY ImpFile ORDER BY ImpTime DESC)
  FROM Table1
)
SELECT
  Name = CASE x.IsAgg WHEN 1 THEN '' ELSE agg.Name END,
  agg.ImpFile,
  agg.ImpTime
FROM agg
  INNER JOIN (SELECT 0 UNION ALL SELECT 1) x (IsAgg) ON x.IsAgg = 0 OR agg.rn = 1
ORDER BY
  agg.ImpTimeMax DESC,  /* the primary order for groups */
  agg.ImpFile    ASC ,  /* in case two or more groups have the same max time */
  x.IsAgg        DESC,  /* the group summary row goes first */
  agg.ImpTime    DESC,  /* or: agg.rn ASC */
  agg.Name       ASC    /* in case two or more people have the same time */

When run on SQL Fiddle, this produces the following output for your example:

NAME   IMPFILE   IMPTIME
-----  --------  -----------------------
       Import12  2012-05-16 09:55:37.387
Bart   Import12  2012-05-16 09:55:37.387
Sasha  Import12  2012-05-16 09:55:37.385
John   Import12  2012-05-16 09:55:37.384
       Imp01     2012-05-16 09:54:02.478
Ann    Imp01     2012-05-16 09:54:02.478
Mark   Imp01     2012-05-16 09:54:02.477
Sam    Imp01     2012-05-16 09:54:02.477

And note that I temporarily defined ImpTime as a varchar, not as a datetime, just for the sake of a better demonstration, because, like I said, datetime's granularity would result in slightly different values stored (and, accordingly, slightly different output produced).

回答4:

You probably want to scrub the milliseconds: http://www.sqlfiddle.com/#!3/35065/2

select Name, ImpFile, 

   ImpTimeX = 
      DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime)

from tbl

union

select distinct '', ImpFile, 

   ImpTimeX = 
       MAX(DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime))

from tbl 
group by ImpFile
order by ImpTimeX desc, Name

Output:

NAME           IMPFILE        IMPTIMEX
               Import12       May, 16 2012 09:55:37-0700
Bart           Import12       May, 16 2012 09:55:37-0700
John           Import12       May, 16 2012 09:55:37-0700
Sasha          Import12       May, 16 2012 09:55:37-0700
               Imp01          May, 16 2012 09:54:02-0700
Ann            Imp01          May, 16 2012 09:54:02-0700
Mark           Imp01          May, 16 2012 09:54:02-0700
Sam            Imp01          May, 16 2012 09:54:02-0700

Technique for scrubbing the milliseconds sourced here: SQL Server remove milliseconds from datetime

---

If you want to retain and show the original time, just do this: http://www.sqlfiddle.com/#!3/35065/1

with a as(

  select Name, ImpFile, 

     ImpTimeX = 
        DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime),

     ImpTime

  from tbl

  union

  select distinct '', ImpFile, 

     ImpTimeX = 
         MAX(DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime)),

     MAX(ImpTime)

  from tbl 
  group by ImpFile

)
select Name, ImpFile, ImpTime
from a
order by ImpTimeX desc, Name

I think SqlFiddle doesn't show the milliseconds. Please try the second query on your Sql Server there, I'm currently on other OS now, I cannot see the second query's actual output

---

Here's the output of the second query, with the datetime intact, tested on SSMS:

Name    ImpFile         ImpTime     
        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
        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

回答5:

It looks like the order by is only getting applied to the second select in your union query.

Try using a subselect to create a temporary table to apply the order by to:

select Name, ImpFile, ImpTime from (
select Name, ImpFile, ImpTime from people
union
select distinct '', ImpFile, max(ImpTime) from people group by ImpFile
) order by ImpTime desc, Name

回答6:

You can get the desired output with order by ImpFile desc,Name

select Name, ImpFile, ImpTime
    from dbo.tbl_stack 
    union
    select distinct '', ImpFile, max(ImpTime)
    from dbo.tbl_stack 
    group by ImpFile
    order by ImpFile desc,Name 

Here's the output

Name    ImpFile         ImpTime 
        Import12        2012-05-16 09:55:37.387
Bar     Import12        2012-05-16 09:55:37.387
John    Import12        2012-05-16 09:55:37.383
Sasha   Import12        2012-05-16 09:55:37.387
        Imp01       2012-05-16 09:54:02.477
Ann     Imp01       2012-05-16 09:54:02.477
Mark    Imp01       2012-05-16 09:54:02.477
Sam     Imp01           2012-05-16 09:54:02.477