Counting Only certain columns - 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

Counting Only certain columns

Author

Topic

benmcr
Starting Member

1 Post

Posted - 2010-08-31 : 20:13:49

Table fields include logid,DeviceID, Meter, Date. my primary key is logid a log is made daily for each deviceid. exporting system has an error the meter will not change from one day to the next. to find these repeats i can go a few ways the way i feel is easiest would to count meter repeats but that limits the columns i can include. if i include date it makes all rows unique nothing gets counted is there a way to exclude this date column from the count funtion?

Or is there a better way to find the last unique entry and date for each device would be more ideal?

im generating this report for an account with 29 devices

example data set
deviceid meter date
1 5 1/1
2 6 1/1
3 9 1/1
1 5 1/2
2 8 1/2
3 11 1/2
1 5 1/3
2 9 1/3
3 11 1/3

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-31 : 21:09:35

you want to use a correlated subquery, or a CTE (common table expression).

i don't have enough information to understand fully, but it sounds like you want to something like the following

SELECT	t.*
FROM	yourTable t
JOIN	(
	SELECT	DeviceId, Max(date) dt
	FROM	yourTable
) x
On	x.DeviceId = t.DeviceId
And	t.date = x.dt

If you could post the expected output, it would help us to be more specific.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources