Solved

Compare rows in SQL

Posted on 2014-04-15
13
297 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
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 65

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 40

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 65

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

770 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