Deleting repeated rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting repeated rows

Author

Topic

imolinar
Starting Member

2 Posts

Posted - 2008-07-15 : 07:55:08

I have this SQL query:

SELECT
RECORDTIME, ( CASE WHEN CDBHISTORIC.ID = 8795 THEN CDBHISTORIC.FORMATTEDVALUE END ) AS FLOW_1, ( CASE WHEN CDBHISTORIC.ID = 8796 THEN CDBHISTORIC.FORMATTEDVALUE END ) AS FLOW_2
FROM
CDBHISTORIC JOIN CDBPOINT USING ( ID )
WHERE
( CDBHISTORIC.ID IN ( 8795, 8796 ) ) AND ( CDBHISTORIC.RECORDTIME BETWEEN { OPC 'Y' } AND { OPC 'Y + 1Y' } )
ORDER BY
"RecordTime" ASC

and I get as a result something like this:

Time Flow_1 Flow_2
12:05 34.6 NULL
12:05 NuLL 23.4

There is a row for each parameter even though it is the same recordtime.

How do I do it so the results for a specific time are shown on the same row as this:
Time Flow_1 Flow_2
12:05 34.6 23.4

I tried grouping by Recordtime but it says
"Error: all columns must be grouping columns or summary functions"
How do i get around this?

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:06:38

This is a MICROSOFT SQL Server forum.
Maybe you will have better luck at www.dbforums.com?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:09:18

Use aggregation similar to this

SELECT		RECORDTIME,
		MAX(CASE
			WHEN CDBHISTORIC.ID = 8795 THEN CDBHISTORIC.FORMATTEDVALUE
		END) AS FLOW_1,
		MAX(CASE
			WHEN CDBHISTORIC.ID = 8796 THEN CDBHISTORIC.FORMATTEDVALUE
		END) AS FLOW_2
FROM		CDBHISTORIC
JOIN		CDBPOINT USING (ID)
WHERE		CDBHISTORIC.ID IN (8795, 8796)
		AND CDBHISTORIC.RECORDTIME BETWEEN { OPC 'Y' } AND { OPC 'Y + 1Y' }
GROUP BY	RECORDTIME
ORDER BY	RECORDTIME

E 12°55'05.25"
N 56°04'39.16"

imolinar
Starting Member

2 Posts

Posted - 2008-07-15 : 08:20:46

I already tried adding MAX, SUM, etc, but it says "invalid operant", it does not work.....Any other ideas???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:36:59

Which RDBMS are you using?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources