• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

sql syntax

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
newbie27
Asked:
newbie27
  • 5
  • 3
  • 3
  • +3
5 Solutions
 
mbizupCommented:
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
 
harshada_sonawaneCommented:
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
 
deiaccordCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
RehanYousafCommented:
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
 
newbie27Author Commented:
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
 
deiaccordCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
newbie27Author Commented:
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
 
RehanYousafCommented:
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
 
newbie27Author Commented:
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
 
newbie27Author Commented:
deiaccord query is working good for me !
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Just for note.
My query will work for irrespective of order id..
0
 
deiaccordCommented:
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
 
newbie27Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now