sql query help sqlserver 2000 - 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

SQL Server 2000 Forums

SQL Server Development (2000)

sql query help sqlserver 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-11-09 : 14:29:13

When i executed the query i got the below results

VTYCD, ID, maxeid, maxrslt, maxsx, ndt

3, 138, 1034, 04, 01, 2006-08-11 00:00:00.000
3, 141, 1034, 04, 01, 2006-08-11 00:00:00.000

the values in the table est

ID rslt

138 1
141 4

Please help me in rewriting the query to get the correct results.

VTYCD, ID, maxeid, maxrslt, maxsx, ndt

3, 138, 1034, 04, 01, 2006-08-11 00:00:00.000
3, 141, 1034, 01, 01, 2006-08-11 00:00:00.000

SELECT VTY.VTYCD,B.*
FROM VTY
JOIN
(SELECT info.ID, A.maxeID, A.maxrslt,A.maxsx,info.ndt
FROM info
FULL JOIN
(SELECT info.rmID,
MAX(info.ID) AS maxnid,
MAX(info.EID) AS maxeID,
MAX(info.ndt) AS maxndt,
MAX(est.rslt) AS maxrslt,
MAX(rphc.sx) AS maxsx
FROM info
LEFT JOIN est ON info.ID = est.ID
LEFT JOIN rphc ON info.ID = rphc.ID
GROUP BY info.RMID) AS A
ON info.RMID = A.RMID) AS B
ON VTY.ID = B.ID

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 15:03:55

You need to give the table structures, but it looks like you're mixing up the info.ID and info.rmID columns. You use info.rmID for the join in the subquery aliased as A, and then select the info.ID column in the subquery aliased as B. It seems like you also don't need the extra subquery.

What is your query trying to do, do you want the max values for each VTYCD, or do you want the max values for each rmID and the associated VTYCD for each rmID (which will mean you'd have the same max values repeated for a rmID with various different VTYCDs)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources