Post reply

Select distinct and order by without order by column

  • May 14, 2009 at 1:04 pm

    #209315

    I have a table where each row has a unique timestamp column. In many cases other then the timestamp column, all of the other values are dups of another row. I need to select unique rows but return the data by the timestamp column order. I know that a Select distinct .... order by xx statement requires that the order by column be in the select statement.

    Is there a technique or trick by which I can perform a select distinct with an order by but not include the order by meta-data in the select statement?

  • May 14, 2009 at 1:13 pm

    #994822

    mcginn (5/14/2009)

    I have a table where each row has a unique timestamp column. In many cases other then the timestamp column, all of the other values are dups of another row. I need to select unique rows but return the data by the timestamp column order. I know that a Select distinct .... order by xx statement requires that the order by column be in the select statement.

    Is there a technique or trick by which I can perform a select distinct with an order by but not include the order by meta-data in the select statement?

    row_number over (partition by)can do what you are after;

    simply wrap the query with an outer query and get where the rownumber was 1:

    SELECT * FROM

    (SELECT row_number() OVER (PARTITON BY [col list excluding timestamp] ORDER BY timestamp) AS RW,

    YOURTABLE.*

    FROM YOURTABLE ) MyAlias Where RW=1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 14, 2009 at 1:28 pm

    #994837

    Thank you. Fascinating solution; it works perfectly

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply