Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Compare rows in SQL

I have a variable table in a stored procedure that stores order quantities. each row is differentiated by a version number. sometimes the quantities among same order but different rows/ versions are same, sometimes are different.

data sample:

OrderNumber	VersionNumber	ItemNumber	OrderQuantity
S20026	4	10568	23000.000000000000000000
S20026	5	10568	23000.000000000000000000

Open in new window



I would like to learn of a way to compare each row and select only those that show a different quantity for same order, in the case of sample data above, because the quantities are the same, i would then show the newest version with same quantity.


I hope this is clear, is the only part I am missing n my store procedure.

There is more information in a separate question I asked yesterday: Q_28412460
0
metropia
Asked:
metropia
  • 4
  • 3
  • 2
  • +3
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I would like to learn of a way to compare each row and select only those that show a different quantity for same order,

Translation:  Return a set that has matching Version Numbers, but different Order Quantities.

Copy-paste the below T-SQL into your SSMS, execute to verify that it works, then modify it to your needs
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO

CREATE TABLE #tmp (OrderNumber varchar(10), VersionNumber int, ItemNumber int, OrderQuantity decimal(19,14) )


INSERT INTO #tmp (OrderNumber, VersionNumber, ItemNumber, OrderQuantity)
VALUES 
('S20026', 4, 10568, 23000),   -- same order number, same order quantity
('S20026', 5, 10568, 23000),
('banana', 5, 10568, 23000),  -- Same order number, different order quantity
('banana', 5, 10568, 23555)

SELECT OrderNumber, COUNT(DISTINCT OrderQuantity) 
FROM #tmp
GROUP BY OrderNumber
HAVING COUNT(DISTINCT OrderQuantity) > 1
GO

Open in new window

0
 
awking00Commented:
select OrderNumber, VersionNumber, ItemNumber, OrderQuantity from
(select OrderNumber, VersionNumber, ItemNumber, OrderQuantity,
 row_number() over (partition by OrderNumber, ItemNumber, OrderQuantity order by VersionNumber desc) rn
 from yourtable)
where rn = 1;
0
 
awking00Commented:
Always forgetting the alias for the subquery in SQL Server -
select OrderNumber, VersionNumber, ItemNumber, OrderQuantity from
(select OrderNumber, VersionNumber, ItemNumber, OrderQuantity,
 row_number() over (partition by OrderNumber, ItemNumber, OrderQuantity order by VersionNumber desc) rn
 from yourtable) as x
where x.rn = 1;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
metropiaAuthor Commented:
Hi Jim,

Thank you for your post. I tired to modify your example by removing the create/ insert  temp table script and use my variable table inside the stored procedure that has the exact sample data I posted in question.

SELECT OrderNumber, COUNT(DISTINCT OrderQuantity) 
	FROM @SalesOrders_Quantity_Changes
	GROUP BY OrderNumber
	HAVING COUNT(DISTINCT OrderQuantity) > 1

Open in new window


But I got no record(s) in return. With that data, the results show be record:

S20026      5      10568      23000.000000000000000000
0
 
SharathData EngineerCommented:
I assume that if the quantities are same, you want to display the latest record. If the quantities are different, you want to show both the records. Is that correct? If so, did you try awking's query?
0
 
David ToddSenior DBACommented:
Hi Folks,

To me this is the same as a previous question from this asker:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28412460.html

Regards
  David
0
 
metropiaAuthor Commented:
Hi David,

Yes this question is related to the one from the link you shared.

I am sorry if I made a duplicate question. I have been struggling to get a good understanding of what I need to do, and found myself changing the question somehow.

I will try not to fall on this behavior again.
0
 
Jim P.Commented:
Try using a row number.

SELECT SubQ.OrderNumber, SubQ.VersionNumber, SubQ.ItemNumber, SubQ.OrderQuantity
FROM (SELECT OrderNumber, VersionNumber, ItemNumber, OrderQuantity,
                       ROW_NUMBER ( )      OVER ( PARTITION BY OrderNumber, , ItemNumber, OrderQuantity ORDER By VersionNumber) as SeqNun
           FROM  #TempTbl) As SubQ
WHERE SubQ.SeqNun =1

Open in new window

0
 
awking00Commented:
Jim P.,
Other than ordering the version number in descending order (to get the latest), isn't that the same query that I already posted?
0
 
metropiaAuthor Commented:
I need to close this question, and grant the points to the experts that help answer it. Unfortunately at the time I posted it I did not have a good understanding of what I needed to do, but I think I now know.

I will post a new question, include as much detail as I can and hopefully some of you are still willing to offer a helping hand.

Thank you so much to every one for your help.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Fair enough.  Thanks for the split, and we'll look out for your 'new and improved' question..
0
 
Jim P.Commented:
awking00,

My sincerest apologies.   I didn't see that buried in the query you presented. I was reading quickly and just skimmed by it.

Jim P.
0
 
awking00Commented:
Jim P.,
No problem. I think many of us have all done that at one time or other. :-)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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