asked on

Group By MS SQL Help

I am wanting to GROUP BY NAME only display the most recent TIME entry where TYPE = STATUS and NAME LIKE 'COMM%' WITH WHAT STATE FIELD Data is. ORDERED BY NAME

So my output would be

COMM00 UP
COMM01 DOWN
COMM02 UP
COMM03 UP
COMM04 UP

Database has a table fields are STATION,NAME, TYPE,TIME,VALUE,TASKNAME,STATE,COMMAND,EVENTNUM,PTYPE
Data is like this
COMM      COMM09      Status      2013-04-18 06:25:11.000      1            DOWN (1)                  04/18/2013 06:25:11.000      131396      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:23.000      0            UP (0)                  04/18/2013 06:25:23.000      131666      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:30.000      1            DOWN (1)                  04/18/2013 06:25:30.000      131756      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:38.000      0            UP (0)                  04/18/2013 06:25:38.000      131962      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:45.000      1            DOWN (1)                  04/18/2013 06:25:45.000      132052      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:49.000      0            UP (0)                  04/18/2013 06:25:49.000      132142      COMLIN
COMM      COMM09      Status      2013-04-18 06:25:56.000      1            DOWN (1)                  04/18/2013 06:25:56.000      132606      COMLIN
COMM      COMM09      Status      2013-04-18 06:26:04.000      0            UP (0)                  04/18/2013 06:26:04.000      132696      COMLIN
COMM      COMM09      Status      2013-04-18 06:27:36.000      1            DOWN (1)                  04/18/2013 06:27:36.000      134148      COMLIN
COMM      COMM09      Status      2013-04-18 06:27:40.000      0            UP (0)                  04/18/2013 06:27:40.000      134238      COMLIN

ralmada

if you just want to display the most recent time entry

      select name, max([time])
      from yourtable
      where type = status and name like 'COMM%'
      group by name

if you want to show all columns aside from those two

select * from (
      select *, row_number() over (partition by name order by [time] desc) rn
      from yourtable
      where type = status and name like 'COMM%'
) a
where rn = 1

rayburnelectric

ASKER

I also need the STATE COLUMN.

SELECT TOP (100) PERCENT NAME, MAX(TIME) AS TIME, STATE
FROM dbo.EVENT
WHERE (NAME LIKE 'COMM%') AND (TYPE = 'Status')
GROUP BY NAME
ORDER BY NAME

causes this:
Error Message: Column 'dbo.EVENT.STATE is invalid in the select list because it is not contained in either aggregate function or the GROUP BY clause.'

ASKER CERTIFIED SOLUTION

ralmada

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

rayburnelectric

ASKER

trying it now.

ralmada

or you can do

SELECT a.NAME, a.TIME, a.STATE
FROM dbo.EVENT a
WHERE (a.NAME LIKE 'COMM%') AND (a.TYPE = 'Status') and
a.time = (select max(time) from dbo.event where name = a.name)

Brendt Hess

First, create this query and use it as a driver of your report:

SELECT Max(TIME),
NAME
FROM EVENT
WHERE NAME LIKE 'COMM%'
AND TYPE = 'Status'
GROUP BY NAME

To use this

SELECT EVENT.NAME,
STATE
FROM EVENT
INNER JOIN (
SELECT Max(TIME),
NAME
FROM EVENT
WHERE NAME LIKE 'COMM%'
AND TYPE = 'Status'
GROUP BY NAME
) src
ON src.NAME = EVENT.NAME
AND src.TIME = EVENT.TIME
ORDER BY EVENT.NAME