Sort twice in one column

Question

I am having a slightly strange question asked by a client of mine.

It has a list of data with a date between parentheses:

Foo (14/08/2012) Bar (15/08/2012) Bar (16/09/2012) Xyz (20/10/2012) 

However, he wants the list to display as follows:

 Foo (14/08/2012) Bar (16/09/2012) Bar (15/08/2012) Foot (20/10/2012) 

(note that the second bar rose one position)

So, the logic of this is that the list needs to be sorted by date in ascending order, EXCLUDED, when two lines have the same name ("Bar"). If they have the same name, it should be sorted with the last date at the top, remaining in a different sort order.

Is it even remotely possible? I experimented with many ORDER BY clauses but couldn't find the right one. Does anyone have an idea?

I should have indicated that this data comes from a table in the sql server database (name and date are in two different columns). Therefore, I am looking for an SQL query that can do the sorting I want.

(I shook this example a bit, so if you need more context, feel free to ask)

Answer 1

It works, I think

 declare @t table (data varchar(50), date datetime) insert @t values ('Foo','2012-08-14'), ('Bar','2012-08-15'), ('Bar','2012-09-16'), ('Xyz','2012-10-20') select t.* from @tt inner join (select data, COUNT(*) cg, MAX(date) as mg from @t group by data) tc on t.data = tc.data order by case when cg>1 then mg else date end, date desc 

produces

 data date ---------- ----------------------- Foo 2012-08-14 00:00:00.000 Bar 2012-09-16 00:00:00.000 Bar 2012-08-15 00:00:00.000 Xyz 2012-10-20 00:00:00.000 

Answer 2

A better performance way than any other published answer is to simply do it completely with ORDER BY , not JOIN or with CTE :

 DECLARE @t TABLE (myData varchar(50), myDate datetime) INSERT INTO @t VALUES ('Foo','2012-08-14'), ('Bar','2012-08-15'), ('Bar','2012-09-16'), ('Xyz','2012-10-20') SELECT * FROM @t t1 ORDER BY (SELECT MIN(t2.myDate) FROM @t t2 WHERE t2.myData = t1.myData), T1.myDate DESC 

This does exactly what you request and will work with any indexes and much better with large amounts of data than any other answers.

Also, it’s much more clear what you are actually trying to do here, instead of masking the real logic with the complexity of the connection and checking the number of elements combined.

Answer 3

This method uses analytic functions to sort, it only requires one SELECT from your table.

The internal query finds spaces where the name changes. These spaces are used to identify groups in the next query, and the outer query performs the final sort by these groups.

I tried here (SQL Fiddle) with extended test data.

 SELECT name, dat FROM ( SELECT name, dat, SUM(gap) over(ORDER BY dat, name) AS grp FROM ( SELECT name, dat, CASE WHEN LAG(name) OVER (ORDER BY dat, name) = name THEN 0 ELSE 1 END AS gap FROM t ) x ) y ORDER BY grp, dat DESC 

---

Advanced Test Data

 ('Bar','2012-08-12'), ('Bar','2012-08-11'), ('Foo','2012-08-14'), ('Bar','2012-08-15'), ('Bar','2012-08-16'), ('Bar','2012-09-17'), ('Xyz','2012-10-20') 

Result

 Bar 2012-08-12 Bar 2012-08-11 Foo 2012-08-14 Bar 2012-09-17 Bar 2012-08-16 Bar 2012-08-15 Xyz 2012-10-20 

Answer 4

I think this works, including the case I asked about in the comments:

 declare @t table (data varchar(50), [date] datetime) insert @t values ('Foo','20120814'), ('Bar','20120815'), ('Bar','20120916'), ('Xyz','20121020') ; With OuterSort as ( select *,ROW_NUMBER() OVER (ORDER BY [date] asc) as rn from @t ) --Now we need to find contiguous ranges of the same data value, and the min and max row number for such a range , Islands as ( select data,rn as rnMin,rn as rnMax from OuterSort os where not exists (select * from OuterSort os2 where os2.data = os.data and os2.rn = os.rn - 1) union all select i.data,rnMin,os.rn from Islands i inner join OuterSort os on i.data = os.data and i.rnMax = os.rn-1 ), FullIslands as ( select data,rnMin,MAX(rnMax) as rnMax from Islands group by data,rnMin ) select * from OuterSort os inner join FullIslands fi on os.rn between fi.rnMin and fi.rnMax order by fi.rnMin asc,os.rn desc 

It works by first calculating the initial ordering in the OuterSort CTE. Then, using two CTEs ( Islands and FullIslands ), we calculate the parts of this ordering in which the same data value appears in adjacent rows. Having done this, we can calculate the final ordering by any value that all adjacent values ​​will have (for example, the smallest number of rows of the "island" to which they belong), and then in the "island" we use the reverse side of the originally calculated sort order.

Note that this may not be very efficient for large datasets. In the data samples, it is displayed as requiring 4 scans of the base table table, as well as a coil.

Answer 5

Try something like ...

 ORDER BY CASE date WHEN '14/08/2012' THEN 1 WHEN '16/09/2012' THEN 2 WHEN '15/08/2012' THEN 3 WHEN '20/10/2012' THEN 4 END 

In MySQL, you can:

 ORDER BY FIELD(date, '14/08/2012', '16/09/2012', '15/08/2012', '20/10/2012') 

In potgres, it can create a FIELD function

 CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$ SELECT COALESCE((SELECT i FROM generate_series(1, array_upper($2, 1)) gs(i) WHERE $2[i] = $1), 0); $$ LANGUAGE SQL STABLE 

If you do not want to use CASE, you can try to find an implementation of the FIELD function for SQL Server.