Select Corresponding to Row from the Same Table SQL Server

Select corresponding to row from the same table SQL Server

I'm not really smart from your description, however, the result can be achieved using the following query

select your_table.*
from your_table 
join
(
  select BlilShortName, max(billversion) bmax
  from your_table
  group by BlilShortName
) t on your_table.billversion = t.bmax and your_table.BlilShortName = t.BlilShortName

From my experience it can be faster in some cases when compared to row_number solution which always uses sequential scan.

PERFORMANCE BONUS

Since there is a discussion regarding the efficiency I dare to add simple test

IF OBJECT_ID('dbo.GTable', 'U') IS NOT NULL  DROP TABLE dbo.GTable
SELECT TOP 1000000
      NEWID() id, 
      ABS(CHECKSUM(NEWID())) % 100 group_id, 
      ABS(CHECKSUM(NEWID())) % 10000 orderby
 INTO GTable
FROM    sys.sysobjects
CROSS JOIN sys.all_columns

SET STATISTICS TIME on
-- GROUP BY version
select t1.*
from gtable t1
join
    (
      SELECT group_id, max(orderby) gmax
      from gtable
      group by group_id
    ) t2 on t1.group_id = t2.group_id and t1.orderby = t2.gmax

-- WINDOW FUNCTION version
select t.id, t.group_id, t.orderby
from
(
select *, 
       dense_rank() over (partition by group_id order by orderby desc) rn
from gtable 
) t
where t.rn = 1

If I run this on my server then the performance of GROUP BY version is more than twice better than the window function version. Moreover, if I create index

CREATE NONCLUSTERED INDEX ix_gtable_groupid_orderby
    ON [dbo].[GTable] (group_id,orderby) INCLUDE (id)

then the performance is even more than three times better, whereas the performance of window function solution is the same since it uses sequential scan despite the index.

Select rows by corresponding columns in one table

Use a self-join. You can use the same table twice in the query; you need to assign aliases to them.

select t1.PersonId, t1.LastName, t2.LastName
from person t1
left join person t2 on t1.boss = t2.PersonId

You need outer join (LEFT JOIN) to keep rows with the null values in boss attribute.

Select rows where there is a one-to-one match between two columns in same table SQL

create table #one_to_one
(id_1 int, name varchar(20), dt date, id_2 int)

insert into #one_to_one values( 487,   'Joe',    '09/06/2004'  ,    332)
insert into #one_to_one values( 731,   'Mike',    '06/01/2004' ,     116)
insert into #one_to_one values(487,   'Joe',    '09/06/2004'  ,    354)
insert into #one_to_one values( 777,    'Rich',    '01/01/2002',      455)
insert into #one_to_one values( 745,    'Mike',    '06/01/2004',      116)


select id_1, name, dt, id_2
from (select *, count(*) over(partition by id_1) as id_1_count,
               count(*) over(partition by id_2) as id_2_count
      from #one_to_one) res
where id_1_count = 1 and id_2_count = 1;

How can I get specific rows in a table by the value of a specific queried column of a different table?

If I understood your question correctly then you can try this

SELECT * FROM table2 WHERE IC = (SELECT IC FROM table1 WHERE B='B1')

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

Select Rows From First Table, For Same Column Values in Second Table

I guess you want to know all ID's that have no row in DealInterestTable with value Y in column UserModified ?

If that is correct, then this query might help you

select d.Deal_ID
from   Deal d
where  not exists (select 1 from DealInterestTable it 
                   where it.ARIDNR = d.Deal_ID 
                   and it.UserModfied = 'Y')

---

Related Topics