November 23, 2011 at 4:12 am
Hi,
I have the follwoing seperate queries that I need wrapped up into one row per order with speerate columns.
As I'm still relatively bew to SQL I'm struggling to find a way how to do this. Can anyone help?
select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery1
where RowNum = 1
--
(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery2
where RowNum = 2)
--
(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery3
where RowNum = 3)
--
(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery4
where RowNum = 4)
--
(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery5
where RowNum = 5)
--
(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery6
where RowNum = 6)
November 23, 2011 at 8:39 am
Check out the last 2 articles linked in my signature.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply