Geek Answers Handbook

Multiple Column Order

I need to order data in two columns. How to do it?

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 using 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 use this query, but it does not order the table by name, only in order by name, when time has the same value for several 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 a table as follows:

 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 a way to order these two columns at the same time?

EDIT What happens when I use order by ImpFile DESC, ImpTime desc ?
This gives me a result table as follows:

 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
+8
sql sql-server sql-server-2005
source share
6 answers

Get the leader of each group and sort them in descending order:

 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 attach followers to the leader and get the leader 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 request is that we bind followers (those who have a name) to their leadership time (TimeGroup) based on ImpFile. The leader and his followers have the same time groups, 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 his followers, just put the 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

+5
source share

Why you can do it like this:

 order by ImpFile DESC, ImpTime desc

No, this does not lead to what you show. This leads to the following:

  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
+6
source share

First of all, you should understand that with granularity of datetime type, SQL Server cannot distinguish between 2012-05-16 09:55:37.384 and 2012-05-16 09:55:37.385 : both will be saved as 2012-05-16 09:55:37.384 .

Bearing this in mind and assuming that you want to sort the groups by MAX(ImpTime) DESC , as well as the detail lines on ImpTime DESC , you can 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 in SQL Fiddle, this leads to 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 varchar and not as datetime , just for a better demonstration, because, as I said, datetime granularity would lead to slightly different values ​​(and, accordingly, a slightly different output signal).

+2
source share

You probably want to clear 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 cleaning milliseconds obtained here: SQL Server removes milliseconds from date and time

If you want to save and show the original time, just do the following: 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 does not show milliseconds. Try to execute the second request on your Sql server, I am now on a different OS, I do not see the second output of the request

Here, the output of the second request with an intact datetime is checked 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
+1
source share

It looks like order by applies only to the second select in your union request.

Try using subselect to create a temporary table to apply 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
0
source share

You can get the desired result by order of 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 is the conclusion

 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
0
source share

More articles:


All Articles