SQL Sort by ID and value in 2 different tables

Question

SQL Sort by ID and value in 2 different tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

Currently I need to make a query to 2 tables where the data have several equal columns. The problem is that the ordering must be done based on how the order of table A is structured, but at the same time taking the value of the ValueOrder column of table B consecutively and ascending. Sample data:

declare @table1 table(idMetadata int, Orden int identity)
insert into @table1 values (15)
insert into @table1 values (16)
insert into @table1 values (17)

select * from @table1

declare @table2 table(idDet int, idEnc int, IDMetadata int, OrderValue int, Orden int null)
insert into @table2 values (185441, 22008, 15, 7, null)
insert into @table2 values (187777, 22269, 15, 7, null)
insert into @table2 values (211259, 24925, 15, 7, null)
insert into @table2 values (251476, 29431, 15, 4, null)
insert into @table2 values (185442, 22008, 16, 6, null)
insert into @table2 values (187778, 22269, 16, 6, null)
insert into @table2 values (211260, 24925, 16, 6, null)
insert into @table2 values (251477, 29431, 16, 5, null)
insert into @table2 values (251478, 29431, 17, 6, null)

update t2
set t2.Orden = t1.Orden
from @table1 t1
inner join @table2 t2
on t1.idMetadata = t2.IDMetadata

I was able to make the following selection:

SELECT distinct A.*
FROM @table2 B1 
LEFT join @table2 A
on A.idMetadata in (select a.idMetadata from @table1 A)
AND A.Orden in (select a.Orden from @table1 A)
where b1.OrderValue > a.OrderValue

But it shows me the following result:

What I need is to create a generic query that shows only those records in table B that have the same metadata and order of table A, that have internally in table B the same "idEnc" but whose field "OrderValue" be consecutive and ascending as shown in the following image with the fields marked in yellow:

Greetings

sql sql-server

asked by Adrian87 05.05.2016 в 22:46

source

2 answers

For nested in python identify items in combobox in c # and xaml?

score 1 · Answer 1

Your code is correct, only the last one you should make a query giving an order to all your fields

declare @table1 table(idMetadata int, Orden int identity)
insert into @table1 values (15)
insert into @table1 values (16)
insert into @table1 values (17)

select * from @table1

declare @table2 table(idDet int, idEnc int, IDMetadata int, OrderValue int, Orden int null)
insert into @table2 values (185441, 22008, 15, 7, null)
insert into @table2 values (187777, 22269, 15, 7, null)
insert into @table2 values (211259, 24925, 15, 7, null)
insert into @table2 values (251476, 29431, 15, 4, null)
insert into @table2 values (185442, 22008, 16, 6, null)
insert into @table2 values (187778, 22269, 16, 6, null)
insert into @table2 values (211260, 24925, 16, 6, null)
insert into @table2 values (251477, 29431, 16, 5, null)
insert into @table2 values (251478, 29431, 17, 6, null)

--select * from @table2
update t2
set t2.Orden = t1.Orden
from @table1 t1
inner join @table2 t2
on t1.idMetadata = t2.IDMetadata
select * from @table2 order by idEnc asc,IDMetadata asc,ordervalue asc,orden asc

score 0 · Answer 2

This is my option

[1] As you are interested only in the fields of A, you care both to make distinct A. * and distinct B1. *

[2] Then, in the third line, you can bring the duplicate records. It should be something like this JOIN (select idDet distinct from @ table2 group by idDet having count (*) > 1) A. By removing the LEFT what we have at this point is:

SELECT distinct B1. *

FROM @ table2 B1

JOIN (select idDet distinct from @ table2 group by idDet having count (*) > 1) A

Now you have the unique fields in table 2. You just need to join the first table

LEFT JOIN @ table1 B ON B.ORDER = B1.OrderValue

Now it would be a matter of adding the desired order