Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Compare rows in SQL

Posted on 2014-04-15
13
Medium Priority
?
315 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 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

972 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