Solved

Compare rows in SQL

Posted on 2014-04-15
13
311 Views
Last Modified: 2014-04-17
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
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 80 total points
ID: 40001719
>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
 
LVL 32

Accepted Solution

by:
awking00 earned 400 total points
ID: 40001730
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
 
LVL 32

Expert Comment

by:awking00
ID: 40001738
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:metropia
ID: 40001758
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
 
LVL 41

Expert Comment

by:Sharath
ID: 40002155
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
 
LVL 35

Expert Comment

by:David Todd
ID: 40002617
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
 

Author Comment

by:metropia
ID: 40002778
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 20 total points
ID: 40003026
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
 
LVL 32

Expert Comment

by:awking00
ID: 40003815
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
 

Author Closing Comment

by:metropia
ID: 40004024
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40004038
Fair enough.  Thanks for the split, and we'll look out for your 'new and improved' question..
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 40004706
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
 
LVL 32

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

626 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