SQL Max - 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

SQL Max

Author

Topic

domleg
Starting Member

3 Posts

Posted - 2007-09-04 : 12:32:07

I am trying to write a simple query that can get the data for cows in a database regarding milkings etc...

The problem comes when I add the calving date to the query and it duplicates all the other rows due to the fact the animal has more than one calving date.

I want to only show the current (most recent) calving date in the query.

My code that works is as follows:

SELECT LelyCow.iUserNumber, LelyDevice.iAddress, LelyDeviceVisit.tVisitDateTime, LelyDeviceVisit.tEndVisitDateTime,
LelyAstronautVisit.tpVisitDuration, LelyDeviceVisit.iVisitStatus, LelyMilkVisitData.nMilkYield, LelyCow.dBirthDate, LelyLactationProduction.dCalvingDate

FROM LelyCow INNER JOIN
LelyDeviceVisit ON LelyCow.PK_LelyCow = LelyDeviceVisit.FK_LelyCow INNER JOIN
LelyDevice ON LelyDeviceVisit.FK_LelyDevice = LelyDevice.PK_LelyDevice INNER JOIN
LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit LEFT JOIN
LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit INNER JOIN
LelyLactationProduction ON LelyCow.PK_LelyCow = LelyLactationProduction.FK_LelyCow

WHERE tEndVisitDateTime BETWEEN '2007-08-01 00:00:00.000' AND '2007-08-21 23:59:59.999'
order by tEndVisitDateTime DESC

gives me the following problem:

69 102 2007-08-21 23:51:44.000 2007-08-21 23:59:05.000 441 1 10.0 1997-09-02 00:00:00.000 2006-02-03 00:00:00.000
69 102 2007-08-21 23:51:44.000 2007-08-21 23:59:05.000 441 1 10.0 1997-09-02 00:00:00.000 2005-02-18 00:00:00.000
69 102 2007-08-21 23:51:44.000 2007-08-21 23:59:05.000 441 1 10.0 1997-09-02 00:00:00.000 2004-02-20 00:00:00.000
69 102 2007-08-21 23:51:44.000 2007-08-21 23:59:05.000 441 1 10.0 1997-09-02 00:00:00.000 2003-01-09 00:00:00.000
69 102 2007-08-21 23:51:44.000 2007-08-21 23:59:05.000 441 1 10.0 1997-09-02 00:00:00.000 2007-03-09 00:00:00.000

As you can see the cow 69 has 5 rows showing the same data except different calving dates.

I just want the most current date to be displayed for this unique milking row.

So I tried the following:

SELECT LelyCow.iUserNumber, LelyDevice.iAddress, LelyDeviceVisit.tVisitDateTime, LelyDeviceVisit.tEndVisitDateTime,
LelyAstronautVisit.tpVisitDuration, LelyDeviceVisit.iVisitStatus, LelyMilkVisitData.nMilkYield, LelyCow.dBirthDate,
max(LelyLactationProduction.dCalvingDate) as CalvingDate

FROM LelyCow INNER JOIN
LelyDeviceVisit ON LelyCow.PK_LelyCow = LelyDeviceVisit.FK_LelyCow INNER JOIN
LelyDevice ON LelyDeviceVisit.FK_LelyDevice = LelyDevice.PK_LelyDevice INNER JOIN
LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit LEFT JOIN
LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit INNER JOIN
LelyLactationProduction ON LelyCow.PK_LelyCow = LelyLactationProduction.FK_LelyCow

WHERE tEndVisitDateTime BETWEEN '2007-08-01 00:00:00.000' AND '2007-08-21 23:59:59.999'
order by tEndVisitDateTime DESC

And I get several errors.

Msg 8118, Level 16, State 1, Line 1
Column 'LelyCow.iUserNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyDevice.iAddress' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyDeviceVisit.tVisitDateTime' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyDeviceVisit.tEndVisitDateTime' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyAstronautVisit.tpVisitDuration' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyDeviceVisit.iVisitStatus' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyMilkVisitData.nMilkYield' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'LelyCow.dBirthDate' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Help please!!!!!!!!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-04 : 12:34:13

when doing aggregates you have to group on the columns you're not aggregating on.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 12:38:07

[code]SELECT d.iUserNumber,
d.iAddress,
d.tVisitDateTime,
d.tEndVisitDateTime,
d.tpVisitDuration,
d.iVisitStatus,
d.nMilkYield,
d.dBirthDate,
d.dCalvingDate
FROM (
SELECT LelyCow.iUserNumber,
LelyDevice.iAddress,
LelyDeviceVisit.tVisitDateTime,
LelyDeviceVisit.tEndVisitDateTime,
LelyAstronautVisit.tpVisitDuration,
LelyDeviceVisit.iVisitStatus,
LelyMilkVisitData.nMilkYield,
LelyCow.dBirthDate,
LelyLactationProduction.dCalvingDate,
ROW_NUMBER() OVER (PARTITION BY LelyCow.iUserNumber ORDER BY LelyLactationProduction.dCalvingDate DESC) AS RecID
FROM LelyCow
INNER JOIN LelyDeviceVisit ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow
INNER JOIN LelyDevice ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice
INNER JOIN LelyAstronautVisit ON LelyAstronautVisit.FK_LelyDeviceVisit = LelyDeviceVisit.PK_LelyDeviceVisit
INNER JOIN LelyLactationProduction ON LelyLactationProduction.FK_LelyCow = LelyCow.PK_LelyCow
LEFT JOIN LelyMilkVisitData ON LelyMilkVisitData.FK_LelyDeviceVisit = LelyDeviceVisit.PK_LelyDeviceVisit
WHERE LelyDeviceVisit.tEndVisitDateTime >= '2007-08-01'
LelyDeviceVisit.tEndVisitDateTime < '2007-08-22'
) AS d
WHERE d.RecID = 1
ORDER BY d.tEndVisitDateTime DESC

SELECT LelyCow.iUserNumber,
LelyDevice.iAddress,
LelyDeviceVisit.tVisitDateTime,
LelyDeviceVisit.tEndVisitDateTime,
LelyAstronautVisit.tpVisitDuration,
LelyDeviceVisit.iVisitStatus,
LelyMilkVisitData.nMilkYield,
LelyCow.dBirthDate,
MAX(LelyLactationProduction.dCalvingDate) AS LastCalvingDate
FROM LelyCow
INNER JOIN LelyDeviceVisit ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow
INNER JOIN LelyDevice ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice
INNER JOIN LelyAstronautVisit ON LelyAstronautVisit.FK_LelyDeviceVisit = LelyDeviceVisit.PK_LelyDeviceVisit
INNER JOIN LelyLactationProduction ON LelyLactationProduction.FK_LelyCow = LelyCow.PK_LelyCow
LEFT JOIN LelyMilkVisitData ON LelyMilkVisitData.FK_LelyDeviceVisit = LelyDeviceVisit.PK_LelyDeviceVisit
WHERE LelyDeviceVisit.tEndVisitDateTime >= '2007-08-01'
LelyDeviceVisit.tEndVisitDateTime < '2007-08-22'
GROUP BY LelyCow.iUserNumber,
LelyDevice.iAddress,
LelyDeviceVisit.tVisitDateTime,
LelyDeviceVisit.tEndVisitDateTime,
LelyAstronautVisit.tpVisitDuration,
LelyDeviceVisit.iVisitStatus,
LelyMilkVisitData.nMilkYield,
LelyCow.dBirthDate
ORDER BY LelyDeviceVisit.tEndVisitDateTime DESC[/code]

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

domleg
Starting Member

3 Posts

Posted - 2007-09-04 : 12:45:52

Gives me this now:

Msg 195, Level 15, State 10, Line 20
'ROW_NUMBER' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 12:47:57

Ok, so you are not using SQL Server 2005.
Did you try the second query?

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

domleg
Starting Member

3 Posts

Posted - 2007-09-04 : 13:20:58

Thanks, works great, I must see what I did wrong and try to understand it, just getting my feet wet by the looks of it or not even the tip of my shoe :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources