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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy