help on a select query - SQL Server Forums

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

russs
Starting Member

12 Posts

Posted - 2013-06-21 : 10:30:49

thank you, but is there another way with the "standrd" sql functions ?

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

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-21 : 10:59:19

you're welcome.

Be One with the Optimizer
TG

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources