Complicated query with grouping.

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 2005 Forums

Transact-SQL (2005)

Complicated query with grouping.

Author

Topic

tpayne
Starting Member

18 Posts

Posted - 2009-03-19 : 13:38:55

I need to figure out how to join these to tables. The parent is REQ_PDC_UPDATE_FUNDING_DOC. I need to group it like this.
SELECT AMMENDED_BK_FND_ID, MAX(AMMENDED_BK_FND_SEQ_ID),PDC_UPDATE_ID
FROM REQ_PDC_UPDATE_FUNDING_DOC FD
GROUP BY AMMENDED_BK_FND_ID, PDC_UPDATE_ID

Then I need to join it to this table. REQ_PDC_UPDATE_FUNDING_LINE The tables are joined by PDC_UPDATE_ID.

I need to get records from this table as follows, but only those records that match from the first query.

SELECT * FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY PDC_UPDATE_ID ORDER BY PDC_UPDATE_FUNDING_LINE_ID DESC) AS ROWNO,
B.SHORT_CODE,
B.PDC_UPDATE_ID,
B.SHORT_CODE_FISCAL_YEAR,
B.NON_DOD_DOCUMENT_NUMBER,
B.MORD,
B.DOCUMENT_NUMBER,
B.PDC_UPDATE_FUNDING_TYPE_ID
FROM REQ_PDC_UPDATE_FUNDING_LINE B) T
WHERE T.ROWNO = 1
ORDER BY T.PDC_UPDATE_ID

thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:50:05

[code]
SELECT *
FROM (
SELECT AMMENDED_BK_FND_ID, MAX(AMMENDED_BK_FND_SEQ_ID),PDC_UPDATE_ID
FROM REQ_PDC_UPDATE_FUNDING_DOC FD
GROUP BY AMMENDED_BK_FND_ID, PDC_UPDATE_ID
)m
INNER JOIN
(
SELECT ROW_NUMBER() OVER(PARTITION BY PDC_UPDATE_ID ORDER BY PDC_UPDATE_FUNDING_LINE_ID DESC) AS ROWNO,
B.SHORT_CODE,
B.PDC_UPDATE_ID,
B.SHORT_CODE_FISCAL_YEAR,
B.NON_DOD_DOCUMENT_NUMBER,
B.MORD,
B.DOCUMENT_NUMBER,
B.PDC_UPDATE_FUNDING_TYPE_ID
FROM REQ_PDC_UPDATE_FUNDING_LINE B) n
ON n.PDC_UPDATE_ID =m.PDC_UPDATE_ID
AND n.ROWNO = 1
ORDER BY m.PDC_UPDATE_ID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources