SQL select query did not work

Question

I have a table like below,

Agent   Group   Skill   Peripheral  Route   DateTime    Variable4
55496   NULL    318735  5135    NULL    7/10/2016 10:40 0000000000???NA ALL?15176600??28324000??
55496   53944   124769  5079    36567   7/10/2016 10:41 0000000000???NA ALL?15176600??28324000??
NULL    NULL    124769  5079    36567   7/10/2016 10:41 0000000000???NA ALL?15176600??28324000??

NULL    NULL    296404  5119    NULL    7/10/2016 10:44 0000000000???NA ALL?15176600??28777000??
55497   53944   124769  5079    36567   7/10/2016 10:45 0000000000???NA ALL?15176600??28777000??
NULL    NULL    124769  5079    36567   7/10/2016 10:45 0000000000???NA ALL?15176600??28777000??

55498   NULL    318735  5135    NULL    7/10/2016 10:46 0000000000???NA ALL?15176600??28928000??
55498   53944   124769  5079    36567   7/10/2016 10:46 0000000000???NA ALL?15176600??28928000??
NULL    NULL    124769  5079    36567   7/10/2016 10:46 0000000000???NA ALL?15176600??28928000??

Here you see the Variable4 is same for the each three records, I need one record for each of Variable4, that should be the latest DateTime among those and the Agent field should not be null

Below is what i want,

55496   53944   124769  5079    36567   7/10/2016 10:41 0000000000???NA ALL?15176600??28324000??
55497   53944   124769  5079    36567   7/10/2016 10:45 0000000000???NA ALL?15176600??28777000??
55498   53944   124769  5079    36567   7/10/2016 10:46 0000000000???NA ALL?15176600??28928000??

Can anyone help me to write an SQL query to achieve this?

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ Unless you provide some actual details we can't do much to help here. — Sean Lange, Jul 11 '16 at 19:32

score 0 · Answer 1 · answered Jul 11 '16 at 19:30

0

Please be more specific, but I believe a general select query would work.

SELECT Agent, Group, Skill, Peripheral, Route, DateTime, Variable4
FROM table_name
WHERE Variable4 NOT NULL
GROUP BY Variable4 ASC;

answered Jul 11 '16 at 19:30

Dakota Wagner

117
2
15

`Group By` doesn't have `ASC` or `DESC`.. `GROUP BY Variable4 ASC` – Vignesh Kumar A Jul 11 '16 at 19:36

score 0 · Answer 2 · answered Jul 11 '16 at 19:34

0

Try like this

SELECT MAX(Agent) Agent, MAX(Group) Group, MAX(Skill) Skill, MAX(Peripheral) Peripheral, MAX(Route) Route, MAX(DateTime) DateTime, Variable4
FROM table_name
GROUP BY Variable4

answered Jul 11 '16 at 19:34

Vignesh Kumar A

27,863
13
63
115

SQL select query did not work

2 Answers2