Solved

sql syntax

Posted on 2013-01-24
15
453 Views
Last Modified: 2013-01-27
Hello Experts
I am trying to write a very simple SQL query to fetch the orders, joining the history with the status = "order_sent" but when there are two records with the same status its returning 2 rows and I only need one which is the latest.

select * from 
  dbo.DShip INNER JOIN
  dbo.DShipDetail ON dbo.DShip.OrderID = dbo.DShipDetail.OrderID INNER JOIN
  dbo.DShipHistory ON dbo.DShipDetail.OrderID = dbo.DShipHistory.OrderID  
  WHERE dbo.DShip.orderid=75971 AND STATUSID = 'ORDER_SENT'


history data
============
orderid	rowid	dateadded		statusid
75971	1	22 December 2012	ORDER_CREATED                 
75971	2	22 December 2012	NOTES_ADDED                   
75971	3	22 December 2012	NOTES_ADDED                   
75971	4	22 December 2012	ORDER_SENT                    
75971	6	22 December 2012	NOTES_ADDED                   
75971	10	28 December 2012	ORDER_INVOICED                
75971	11	08 January 2013		ORDER_RESEND                  
75971	12	08 January 2013		ORDER_SENT                    
75971	14	08 January 2013		NOTES_ADDED                   


I only wanted it to return

75971	12	08 January 2013		ORDER_SENT  

Open in new window

0
Comment
Question by:newbie27
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 25 total points
ID: 38813964
Try this:

select orderid, Max(rowid) AS MostRecentID, dateadded, statusid   FROM
  dbo.DShip INNER JOIN
  dbo.DShipDetail ON dbo.DShip.OrderID = dbo.DShipDetail.OrderID INNER JOIN
  dbo.DShipHistory ON dbo.DShipDetail.OrderID = dbo.DShipHistory.OrderID  
  WHERE dbo.DShip.orderid=75971 AND STATUSID = 'ORDER_SENT'
GROUP BY orderid, dateadded, statusid

Open in new window

0
 
LVL 2

Assisted Solution

by:harshada_sonawane
harshada_sonawane earned 25 total points
ID: 38813970
try this

select o.orderid,oh.rowid,oh.dateadded,oh.statusid             from order o inner join
order_history oh on o.orderid=oh.orderid
inner join
(select orderid      ,max(rowid) rowid from order_history where       statusid='ORDER_SENT'      
groub by orderid
)      oh1 on oh.orderid= oh1.orderid and oh.rowid=oh1.rowid
0
 
LVL 6

Expert Comment

by:deiaccord
ID: 38813974
I can't tell from the info you have given which table tho rowid comes from so I have assumed DShipDetail below.You will need to modify this if it is incorrect but the rest should pretty much do what you want with the additional where clause and sub-select

select * from 
  dbo.DShip INNER JOIN
  dbo.DShipDetail ON dbo.DShip.OrderID = dbo.DShipDetail.OrderID INNER JOIN
  dbo.DShipHistory ON dbo.DShipDetail.OrderID = dbo.DShipHistory.OrderID  
  WHERE dbo.DShip.orderid=75971 AND STATUSID = 'ORDER_SENT'
  --Change the table for rowid in both locations to match the correct table name if this is incorrect
  AND DShipDetail.rowid = (select max(dsd.rowid) from DShipDetail dsd where dsd.OrderID = DShip.OrderID)

Open in new window

0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 25 total points
ID: 38814033
I think what you want is something like
--------------------------------------------------------------------------------------------
DECLARE @DShip TABLE(
	OrderID INT
)
INSERT INTO @DShip VALUES (75971)

--------------------------------------------------------------------------------------------
DECLARE @DShipHistory TABLE(
	OrderID INT,
	RowID INT,
	DateAdded VARCHAR(50),
	StatusID VARCHAR(50)
)
INSERT INTO @DShipHistory VALUES (75971,	1,	'22 December 2012',	'ORDER_CREATED')                 
INSERT INTO @DShipHistory VALUES (75971,	2,	'22 December 2012',	'NOTES_ADDED')               
INSERT INTO @DShipHistory VALUES (75971,	3,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	4,	'22 December 2012',	'ORDER_SENT')                    
INSERT INTO @DShipHistory VALUES (75971,	6,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	10,	'28 December 2012',	'ORDER_INVOICED')                
INSERT INTO @DShipHistory VALUES (75971,	11,	'08 January 2013',		'ORDER_RESEND')                  
INSERT INTO @DShipHistory VALUES (75971,	12,	'08 January 2013',		'ORDER_SENT')                  
INSERT INTO @DShipHistory VALUES (75971,	14,	'08 January 2013',		'NOTES_ADDED') 

--------------------------------------------------------------------------------------------
;WITH cteDShipHistory AS (
	SELECT 
		s.OrderID, 
		MAX(h.RowID)MaxRowID
	FROM 
		@DShip s
		INNER JOIN @DShipHistory h ON s.OrderID = h.OrderID  
	WHERE 
		s.OrderID = 75971 
		AND StatusID = 'ORDER_SENT'
	GROUP BY
		s.OrderID
)
SELECT 
	c.OrderID,
	h.RowID,
	h.DateAdded,
	h.StatusID
FROM
	cteDShipHistory c
	INNER JOIN @DShipHistory h ON c.OrderID = h.OrderID AND c.MaxRowID = h.RowID

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 38814038
Thanks for your input, I have tried two suggestions from the above.
mbizup,
when I try with your exact SQL it works but when I modified a little to include some other details then it started retuning duplicates
please can you check the attached and advise where I am going wrong?

deiaccord,
i have tried your SQL but did not work, I have rowid in history table
please see attached

SELECT     TOP (100) PERCENT dbo.DropShip.DropShipOrderID, dbo.DropShip.DropShipRetailerOrderID,
  
dbo.DropShipDetail.DropShipGiftName, dbo.DropShipDetail.DropShipQty, 
DATEPART(day, dbo.DropShipHistory.DropShipHistDateAdded) AS day, 
DATEPART(month, dbo.DropShipHistory.DropShipHistDateAdded) AS month, 
DATEPART(year, dbo.DropShipHistory.DropShipHistDateAdded) AS year, 
 dbo.Category.CatDesc, Max(rowid) AS RecentlyAdded
FROM         dbo.DropShip INNER JOIN
                      dbo.DropShipDetail ON dbo.DropShip.DropShipOrderID = dbo.DropShipDetail.DropShipOrderID INNER JOIN
                      dbo.Retailer ON dbo.DropShip.RetailerID = dbo.Retailer.RetailerID INNER JOIN
                      dbo.DropShipHistory ON dbo.DropShipDetail.DropShipOrderID = dbo.DropShipHistory.DropShipOrderID INNER JOIN
                      dbo.[User] ON dbo.DropShipHistory.UserID = dbo.[User].UserID INNER JOIN
                      dbo.ProductCategory ON dbo.DropShipDetail.GiftProductCodeID = dbo.ProductCategory.ProdID INNER JOIN
                      dbo.Category ON dbo.ProductCategory.CatID = dbo.Category.CatID
WHERE dbo.DropShip.dropshiporderid=56392 AND (dbo.DropShipHistory.StatusID = 'ORDER_SENT')   
--AND   (dbo.DropShipHistory.DropshipHistDateAdded >= DateAdd(dd, -1, DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)))
--AND   (dbo.DropShipHistory.DropShipHistDateAdded < DateAdd(dd, DateDiff(dd, 0, GetDate()), 0))
AND   (dbo.Category.ShowOnReport = 'True')
GROUP BY dbo.DropShip.DropShipOrderID, dbo.DropShip.DropShipRetailerOrderID, 
dbo.DropShipDetail.DropShipGiftName, dbo.DropShipDetail.DropShipQty,
DATEPART(day, dbo.DropShipHistory.DropShipHistDateAdded), 
DATEPART(month, dbo.DropShipHistory.DropShipHistDateAdded), 
DATEPART(year, dbo.DropShipHistory.DropShipHistDateAdded), 
  dbo.Category.CatDesc


Its returning RowID 4 and 12


select *, dropshiphistory.ROWID from 
  dbo.DropShip INNER JOIN
  dbo.DropShipDetail ON dbo.DropShip.DropShipOrderID = dbo.DropShipDetail.DropShipOrderID INNER JOIN
  dbo.DropShipHistory ON dbo.DropShipDetail.DropShipOrderID = dbo.DropShipHistory.DropShipOrderID  

 WHERE dbo.DropShip.dropshiporderid=75971 and STATUSID = 'ORDER_SENT' AND 
dropshiphistory.ROWID = ( SELECT MAX(ROWID) FROM dbo.DropShipHistory WHERE dbo.DropShip.dropshiporderid=75971)
      
This is not returning any data

Open in new window

0
 
LVL 6

Accepted Solution

by:
deiaccord earned 400 total points
ID: 38814058
I have modified my earlier query based on the extra information you have given which should do the trick now

select * from 
  dbo.DShip INNER JOIN
  dbo.DShipDetail ON dbo.DShip.OrderID = dbo.DShipDetail.OrderID INNER JOIN
  dbo.DShipHistory ON dbo.DShipDetail.OrderID = dbo.DShipHistory.OrderID  
  WHERE dbo.DShip.orderid=75971 AND STATUSID = 'ORDER_SENT'
  AND DShipHistory.rowid = 
	(select max(dsh.rowid) from dbo.DShipHistory dsh 
	 where dsh.OrderID = DShipDetail.OrderID and dsh.statusid = 'ORDER_SENT')

Open in new window

0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 25 total points
ID: 38814063
Hi,

Used RehanQuery.

Check out the query.

--------------------------------------------------------------------------------------------
DECLARE @DShip TABLE(
	OrderID INT
)
INSERT INTO @DShip VALUES (75971)

--------------------------------------------------------------------------------------------
DECLARE @DShipHistory TABLE(
	OrderID INT,
	RowID INT,
	DateAdded VARCHAR(50),
	StatusID VARCHAR(50)
)
INSERT INTO @DShipHistory VALUES (75971,	1,	'22 December 2012',	'ORDER_CREATED')                 
INSERT INTO @DShipHistory VALUES (75971,	2,	'22 December 2012',	'NOTES_ADDED')               
INSERT INTO @DShipHistory VALUES (75971,	3,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	4,	'22 December 2012',	'ORDER_SENT')                    
INSERT INTO @DShipHistory VALUES (75971,	6,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	10,	'28 December 2012',	'ORDER_INVOICED')                
INSERT INTO @DShipHistory VALUES (75971,	11,	'08 January 2013',		'ORDER_RESEND')                  
INSERT INTO @DShipHistory VALUES (75971,	12,	'08 January 2013',		'ORDER_SENT')                  
INSERT INTO @DShipHistory VALUES (75971,	14,	'08 January 2013',		'NOTES_ADDED') 

--------------------------------------------------------------------------------------------
	SELECT 
		*
	FROM @DShip s
		INNER JOIN 
		(SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY RowID DESC) AS Row,*
		 FROM
				@DShipHistory 
		 WHERE
				StatusID = 'ORDER_SENT'
		)H ON s.OrderID = H.OrderID  
	WHERE 
		s.OrderID = 75971 
	AND Row = 1

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Author Comment

by:newbie27
ID: 38814086
Hi deiaccord
Thanks, this is working.

Hi RehanYousaf
thank you for your efforts in building up the query for me that its looking bit complicated for me where deiaccord is looking simple to understand to be honest...

thank you all!
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38814089
Why make things so complicated :-)

--------------------------------------------------------------------------------------------
DECLARE @DShip TABLE(
	OrderID INT
)
INSERT INTO @DShip VALUES (75971)

--------------------------------------------------------------------------------------------
DECLARE @DShipHistory TABLE(
	OrderID INT,
	RowID INT,
	DateAdded VARCHAR(50),
	StatusID VARCHAR(50)
)
INSERT INTO @DShipHistory VALUES (75971,	1,	'22 December 2012',	'ORDER_CREATED')                 
INSERT INTO @DShipHistory VALUES (75971,	2,	'22 December 2012',	'NOTES_ADDED')               
INSERT INTO @DShipHistory VALUES (75971,	3,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	4,	'22 December 2012',	'ORDER_SENT')                    
INSERT INTO @DShipHistory VALUES (75971,	6,	'22 December 2012',	'NOTES_ADDED')                   
INSERT INTO @DShipHistory VALUES (75971,	10,	'28 December 2012',	'ORDER_INVOICED')                
INSERT INTO @DShipHistory VALUES (75971,	11,	'08 January 2013',		'ORDER_RESEND')                  
INSERT INTO @DShipHistory VALUES (75971,	12,	'08 January 2013',		'ORDER_SENT')                  
INSERT INTO @DShipHistory VALUES (75971,	14,	'08 January 2013',		'NOTES_ADDED') 

--------------------------------------------------------------------------------------------
SELECT TOP 1
	*
FROM 
	@DShipHistory s
WHERE 
	s.OrderID = 75971 
	AND StatusID = 'ORDER_SENT'
ORDER BY	
	RowID DESC

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 38814139
Thanks RehanYousaf
I am trying to prepare SQL so that I schedule a weekly report, the OrderID I have used is only as an example, I will have 100s of orders which will come on the report, your latest SQL would only give me the top 1 record out of 100's?

Regards
0
 
LVL 8

Author Comment

by:newbie27
ID: 38814155
deiaccord query is working good for me !
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38814220
Hi,

Just for note.
My query will work for irrespective of order id..
0
 
LVL 6

Expert Comment

by:deiaccord
ID: 38814248
Glad it's working!

The other solutions will need/use a subselect or join based on the rowid to get round hard coding it as in your example, which is what I have also done in my suggestion.  They ultimatly all do pretty much the same thing so it's a question of which style and syntax you find easiest to work with. There's more than one way to do it (TMTOWTDI) as the saying goes!

 harshada_sonawane's suggestion of using group by gets round the need for subselects  but as you found needs modification every time you add new columns to the query!
0
 
LVL 8

Author Comment

by:newbie27
ID: 38815273
Thanks Brichsoft, for your input, I found it slightly tricky to understand the below that is why I have opted the easiest among all, thank you again for the help and time.
(SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY RowID DESC) AS Row,*

deiaccord
Thanks again!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38825742
hi,
row number will generate identity row based on grouping you gave in partition.

let say, this is ur data with two orders.

orderid      rowid      dateadded            statusid
75971      1      22 December 2012      ORDER_CREATED                
75971      3      22 December 2012      NOTES_ADDED                  
75971      4      22 December 2012      ORDER_SENT                    
75972      1      22 December 2012      ORDER_CREATED                
75972      3      22 December 2012      NOTES_ADDED                  
75972      4      22 December 2012      ORDER_SENT

so from row number you will get                    
orderid      rowid      dateadded            statusid            Row
75971      4      22 December 2012      ORDER_SENT        1            
75972      4      22 December 2012      ORDER_SENT              1

In short, treat this as identity column.

More info, check out the link
http://msdn.microsoft.com/en-us/library/ms186734.aspx
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now