CODEWITHSUNDEEP
sqlsql-serverselecttimegroup-by
Aleks
Aleks

Reputation: 469

Group by max date and id

I have the following table:

 id         value       date
 ---------  ---------  ----------
 1          324          2017-05-19 02:00:00
 2          564          2017-05-19 04:00:00
 3          675          2017-05-19 03:00:00
 3          642          2017-05-19 05:00:00
 4          642          2017-05-19 07:00:00
 5          642          2017-05-19 06:00:00
 6          642          2017-05-19 05:00:00
 6          642          2017-05-19 12:00:00
 7          642          2017-05-19 01:00:00
 8          642          2017-05-19 13:00:00
 8          642          2017-05-19 02:00:00

I want to GROUP BY the id column but also select the value and date column from the first row when ordered by date descending, with that id.

The result should be like this:

 id         value       date
 ---------  ---------  ----------
 1          324          2017-05-19 02:00:00
 2          564          2017-05-19 04:00:00
 3          642          2017-05-19 05:00:00
 4          642          2017-05-19 07:00:00
 5          642          2017-05-19 06:00:00
 6          642          2017-05-19 12:00:00
 7          642          2017-05-19 01:00:00
 8          642          2017-05-19 13:00:00

Upvotes: 3

Views: 11523

Answers (5)

user7715598
user7715598

Reputation: 

;With cte( id, value ,date)
AS
(

 SELECT 1, 324,'2017-05-19 02:00:00' Union all
 SELECT 2, 564,'2017-05-19 04:00:00' Union all
 SELECT 3, 675,'2017-05-19 03:00:00' Union all
 SELECT 3, 642,'2017-05-19 05:00:00' Union all
 SELECT 4, 642,'2017-05-19 07:00:00' Union all
 SELECT 5, 642,'2017-05-19 06:00:00' Union all
 SELECT 6, 642,'2017-05-19 05:00:00' Union all
 SELECT 6, 642,'2017-05-19 12:00:00' Union all
 SELECT 7, 642,'2017-05-19 01:00:00' Union all
 SELECT 8, 642,'2017-05-19 13:00:00' Union all
 SELECT 8, 642,'2017-05-19 02:00:00' 
 )
SELECT id
    ,value
    ,DATE
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY id
            ) RNO
    FROM (
        SELECT id
            ,value
            ,MAX([date]) OVER (
                PARTITION BY id ORDER BY id DESC
                ) AS [date]
            ,ROW_NUMBER() OVER (
                PARTITION BY DATE ORDER BY id
                ) seq
        FROM cte
        ) ddt
    ) Final
WHERE Final.RNO = 1
ORDER BY Final.RNO

OutPut

id  value   DATE
---------------------------
1   324     2017-05-19 02:00:00
2   564     2017-05-19 04:00:00
3   642     2017-05-19 05:00:00
4   642     2017-05-19 07:00:00
5   642     2017-05-19 06:00:00
6   642     2017-05-19 12:00:00
7   642     2017-05-19 01:00:00
8   642     2017-05-19 13:00:00

Upvotes: 0

kapilpatwa93
kapilpatwa93

Reputation: 4411

Try this query...

SELECT t1.id,value,t1.date FROM new_table t1   
JOIN 
(SELECT nt.id,max(nt.date) AS date  FROM new_table as nt group by nt.id ) AS t2 
ON t1.date = t2.date AND t1.id = t2.id;

It will work..

Ask if any doubt.

Upvotes: 0

davioooh
davioooh

Reputation: 24666

Something like this should work:

SELECT t1.id, t1.value, t1.date
FROM your_table t1
INNER JOIN (
    SELECT id, MAX(date) date
    FROM your_table
    GROUP BY id
) t2
ON t1.id = t2.id AND t1.date = t2.date

Upvotes: 6

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

select id, value, max(date) 
from table_name 
group by id,value;

Upvotes: 1

dmnoguera
dmnoguera

Reputation: 46

Try this

select id, value, date 
from  name_table
group by id, value, date

Upvotes: 1

Related Questions