SQL Sorting data using 2 different tables

Question

I need to sort data using 2 different tables. In Table A I have the order that is inserted consecutively by the user and in Table B I have the values that I should get using the order from table A and a linked column from the 2 tables called "IdMetadata". Data:

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

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

The main problem is that I have to create a query that gets values from table B and have a consecutive order like table A (rows in yellow) using the field called "OrderValue" with ascending order.

The following query is the basic idea that I have until now

    select distinct t3.idEnc, t3.IDMetadata, t3.OrderValue
    from @table1 t1
    inner join @table2 t2
    on t1.idMetadata = t2.IDMetadata
    inner join @table2 t3
    on t2.idEnc = t3.idEnc
    order by t3.idEnc desc, t3.IDMetadata asc, t3.OrderValue asc

I changed the question a little bit and added some data to use the select that I have.. — Adrian87
– Adrian87, Commented May 5, 2016 at 21:36
what you have is correct with the sample data shown. because the ordervalue corresponding to lower idmetadata is higher you think this is wrong. You will better understand the results when there is more than one ordervalue per idmetadata per idenc. — Vamsi Prabhala
– Vamsi Prabhala, Commented May 5, 2016 at 21:54
I would like to exclude those records from the result set that don't meet the condition of having a consecutive ascending order in table 2. Table 1 defines the order of selecting IdMetadata but in table 2, the OrderValue field in some records are not ascending, example, in some cases IdMetadata 15 value is 7 and Idmetadata 16 is 6, and I want those where the OrderValue from Idmetadata 15 is less than OrderValue from Idmetadata 16, example 4 and 5 (ascending & consecutive order). — Adrian87
– Adrian87, Commented May 5, 2016 at 21:57

Vamsi Prabhala · Accepted Answer · 2016-05-05 22:15:58Z

3

As per your comment, this is what you are trying to do.

select * from @table2 where idenc in (
select distinct t15.idEnc
    from @table1 t1
    inner join (select * from @table2 where IDmetadata = 15) t15
    on t1.idMetadata = t15.IDMetadata
    inner join (select * from @table2 where IDmetadata = 16) t16
    on  t15.idenc=t16.idenc
where t16.ordervalue > t15.ordervalue
    )
order by idenc,idmetadata,ordervalue

answered May 5, 2016 at 22:15

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian87 Over a year ago

Thats a nice answer man ! I've seen in many cases that people use CTE for grouping and ordering records. I think that I gotta give an eye to CTEs for a generic query with n number of records based on your solution. Again, thanks a lot bro !

Collectives™ on Stack Overflow

SQL Sorting data using 2 different tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related