Select corresponding to row from the same table SQL Server

by

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.

More Related Contents:

  1. Get top 1 row of each group
  2. Select top 10 records for each category
  3. Simple Query to Grab Max Value for each ID
  4. T-SQL: Selecting Column Based on MAX(Other Column)
  5. SQL server select distinct rows using most recent value only
  6. Case statement for multiple columns
  7. SQL where clause odd behavior [closed]
  8. SQL Server Exec issue [closed]
  9. Count(*) vs Count(1) – SQL Server
  10. Is a view faster than a simple query?
  11. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  12. How do I delete from multiple tables using INNER JOIN in SQL server
  13. SQL : BETWEEN vs =
  14. SQL Server convert string to datetime
  15. Should I design a table with a primary key of varchar or int?
  16. How do you import a large MS SQL .sql file?
  17. How do I make a composite key with SQL Server Management Studio?
  18. Difference between Subquery and Correlated Subquery
  19. Return number of rows affected by UPDATE statements
  20. Execute Dynamic Query with go in sql
  21. TransactSQL to run another TransactSQL script
  22. How can I get a list of element names from an XML value in SQL Server
  23. Selecting all corresponding fields using MAX and GROUP BY
  24. SQL server identity column values start at 0 instead of 1
  25. How to rename something in SQL Server that has square brackets in the name?
  26. Is there a severe performance hit for using Foreign Keys in SQL Server?
  27. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  28. Date serial in SQL?
  29. Get most common value for each value of another column in SQL
  30. Parameterise table name in .NET/SQL?

Leave a Comment