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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help on a select query

Author  Topic 

russs
Starting Member

12 Posts

Posted - 2013-06-21 : 10:18:29
Hello,

i have the table mytable with columns : code and creation_date (along with other columns)

code does not have a unique constraint on it, is not a PK either

I want to retrieve the "oldest" rows, the one that have their creation_date = min(creation_date) (grouped by code)

the following query did not work:

select *
from mytable
having creation_date = min(creation_date)
group by code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 10:28:09
[code]

select *
from
(
select *,row_number() over (partition by code order by creation_date asc) as seq
from mytable
)t
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

russs
Starting Member

12 Posts

Posted - 2013-06-21 : 10:30:49
thank you, but is there another way with the "standrd" sql functions ?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-21 : 10:31:33
the clause order is wrong. Here are the cliff notes:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

but that still won't work. You'll need something like this:

select t.*
from (
select code, min(creation_date) mindt
from mytable
group by code
) d
join myTable t
on t.code = d.code
and t.creation_date = d.mindt


Be One with the Optimizer
TG
Go to Top of Page

russs
Starting Member

12 Posts

Posted - 2013-06-21 : 10:45:59
Thank you TG !
quote:
Originally posted by TG

the clause order is wrong. Here are the cliff notes:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

but that still won't work. You'll need something like this:

select t.*
from (
select code, min(creation_date) mindt
from mytable
group by code
) d
join myTable t
on t.code = d.code
and t.creation_date = d.mindt


Be One with the Optimizer
TG

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-21 : 10:59:19
you're welcome.

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 11:11:59
quote:
Originally posted by TG

the clause order is wrong



do you mean in the posted query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-21 : 13:32:59
sorry V, I was referring to the original post:
quote:

select *
from mytable
having creation_date = min(creation_date)
group by code


I had a response prepared before seeing your post - I just posted anyway as they seemed to have a problem with row_number() in your solution...

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-22 : 02:24:20
ha.. sorry thought you were referring to my posted one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -