May 14, 2009 at 1:04 pm
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
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
May 14, 2009 at 1:28 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy