Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compare rows in SQL

Posted on 2014-04-15
13
Medium Priority
?
312 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 320 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 1600 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 80 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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