CODEWITHSUNDEEP
sqlsql-servert-sql
LONG
LONG

Reputation: 4610

What is the best way to sort below result

For example:

Name         Date
A            2018-06-02
B            2018-06-03
B            2018-06-01
C            2018-06-01

What is the best way to get:

B            2018-06-03
B            2018-06-01
A            2018-06-02
C            2018-06-01

Sort order is first by Order by Date DESC, but then should follow by all the records for that Name

Upvotes: 2

Views: 74

Answers (5)

cloudsafe
cloudsafe

Reputation: 2504

Use row_number:

declare @t as table ([Name] char(1), [Date] date)

insert into @t values
  ('A', '2018-06-02')
, ('B', '2018-06-03')
, ('B', '2018-06-01')
, ('C', '2018-06-01')


    Select t.*, row_number() over (order by md desc) as r
    from
    (
        select [Name], max([date]) md
        from @t
        group by [Name]
        ) x
    inner join @t t on t.[Name] = x.[Name]

Upvotes: 0

Joe C
Joe C

Reputation: 3993

EDIT

I recommend you use ExLo's answer. What I posted was headed in the right direction with window functionality but his seems to match exactly what you need.

You should post expected results and possibly more test data to get a better answer.

Without testing I believe this will set you down a good path.

You can use Dense_Rank () Over (Order By Date Desc) As DateRank and Row_Number() Over (Partition By Name Order By Date Desc) As NameDateOrder

If the above is a subquery or cte you can select from that and order by DateRank, NameDateOrder

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use window function :

order by count(*) over (partition by name) desc, [date] desc, name

For based on date use max() function instead 

order by max([date]) over (partition by name) desc, [date] desc, name

Upvotes: 3

EzLo
EzLo

Reputation: 14189

Try forcing the max date by each name.

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data

CREATE TABLE #Data (
    Name VARCHAR(10),
    Date DATE)

INSERT INTO #Data (
    Name,
    Date)
VALUES
    ('A', '2018-06-02'),
    ('B', '2018-06-03'),
    ('B', '2018-06-01'),
    ('C', '2018-06-01')

SELECT
    D.Name,
    D.Date
FROM
    #Data AS D
ORDER BY
    MAX(D.Date) OVER (PARTITION BY D.Name ORDER BY D.Date DESC) DESC,
    D.Date DESC,
    D.Name

Upvotes: 5

Thom A
Thom A

Reputation: 95554

This should get you what you want:

WITH VTE AS(
    SELECT [name],
           CONVERT(date,[date]) AS [date] --that isn't confusing
    FROM (VALUES ('A','20180602'),('B','20180603'),('B','20180601'),('C','20180601')) V([Name],[date])),
MaxDate AS (
    SELECT *,
           MAX([date]) OVER (PARTITION BY [name]) AS MaxDate
    FROM VTE)
SELECT [name],[date]
FROM MaxDate
ORDER BY MaxDate DESC,
         [date] DESC,
         [name] ASC;

Upvotes: 2

Related Questions