Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-04-29 : 01:27:17
|
Query with order by datetime retrieve below records. If only first record for the same Id(first column xxx) is needed, what could be the query ? I don't think group by will work as other columns are still required and values may be different.xxx1 15/01/2013 col1 col2 col3...xxx1 13/01/2013 col1 col2 col3...xxx2 13/01/2013 col1 col2 col3...Required Result:xxx1 15/01/2013 col1 col2 col3...xxx2 13/01/2013 col1 col2 col3...Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-29 : 01:33:52
|
[code]SELECT required columns hereFROM(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS Seq,*FROM (your Current query))tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-04-29 : 01:46:32
|
Hi,Thanks for your help.Order by clause throws error, actually this is a sub-query 'The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.'Sorry if I'm asking stupid question, I'm weak in SQL.quote: Originally posted by visakh16
SELECT required columns hereFROM(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS Seq,*FROM (your Current query))tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-04-29 : 01:49:06
|
It works when order by is moved to PARTITION clause. Thanks againquote: Originally posted by visakh16
SELECT required columns hereFROM(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS Seq,*FROM (your Current query))tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-29 : 02:28:26
|
order has to be inside partition by construct. if you've any additional fields to order by put it in outside query after WHERE Seq=1------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|