SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help on a select query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

russs
Starting Member

12 Posts

Posted - 06/21/2013 :  10:18:29  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/21/2013 :  10:28:09  Show Profile  Reply with Quote


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


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

russs
Starting Member

12 Posts

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

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/21/2013 :  10:31:33  Show Profile  Reply with Quote
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 - 06/21/2013 :  10:45:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/21/2013 :  10:59:19  Show Profile  Reply with Quote
you're welcome.

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/21/2013 :  11:11:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/21/2013 :  13:32:59  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/22/2013 :  02:24:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000