Possibly extremely simple SQL Query

Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.

I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:

record_id--org_id--live--version
====== ===== === =====
1----1----0----1
2----2----0----1
3----1----1----2
4----2----0----2

as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.

however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)

in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.

many thanks in advance,

philtry self joining subquery:

select a.*
from table_name a
where a.live = 1 or
(a.live = 0 and a.version = (select max(b.version) from table_name b where a.org_id = b.org_id ))|||spot on. told you it was simple. cheers!

Possible to make a procedure "sleep" or run periodically?

Is it possible to put a "sleep" command in a stored procedure.
I would like to write a procedure that periodically checks some things in my
database, but I don't want them to suck up a lot of resources. If I could
write a procedure that runs in a loop with a 5 minute sleep command, that
would do it.
Or is this the wrong approach in SQL Server? I realize I could set up a
separate scheduled task that executes a procedure using ISQL. Is this a
better method?
Rick Harrison.I don't know enough about the particual need to know if there is a better
way or is this is a good way... I've done things like this in the past using
the WAITFOR command. You'll find info in BOL...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Use SQL Server Agent to schedule the stored procedure?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Hi Rick,
Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out ways to run the
stored procedure (SP) on a schedule basis.
Based on my experience, it's better to use scheduled job IF you want to run your SP in a 5
minutes loop (it does handle that gracefully). The WAITFOR statement is also a good method
to implement this, however, it can only work after a specified time interval has passed or a
specified time is reached, unless you use an explicit loop in your SP.
Additionally, the stored procedure will remain suspended until the WAITFOR completes, this
may affect the performance. However, it can easily be embedded in your script and is not
Agent related. So it depends on your scenario and the needs you want to meet.
Rick, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks! WAITFOR is what I was looking for. I did searches using "sleep" and
"pause", but I didn't think of "wait" or "delay".
Rick.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Osu%23o1QxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I don't know enough about the particual need to know if there is a better
> way or is this is a good way... I've done things like this in the past
using
> the WAITFOR command. You'll find info in BOL...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Thanks! Somehow I had overlooked this feature. I knew it was possible to
schedule maintenance plans, which I do, but I didn't know you could run any
procedure on a schedule.
Rick.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OEAHM7QxDHA.1088@.tk2msftngp13.phx.gbl...
> Use SQL Server Agent to schedule the stored procedure?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Both techniques will be useful for me. Thank you.
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:FTG3OcWxDHA.424@.cpmsftngxa07.phx.gbl...
> Hi Rick,
> Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out
ways to run the
> stored procedure (SP) on a schedule basis.
> Based on my experience, it's better to use scheduled job IF you want to
run your SP in a 5
> minutes loop (it does handle that gracefully). The WAITFOR statement is
also a good method
> to implement this, however, it can only work after a specified time
interval has passed or a
> specified time is reached, unless you use an explicit loop in your SP.
> Additionally, the stored procedure will remain suspended until the WAITFOR
completes, this
> may affect the performance. However, it can easily be embedded in your
script and is not
> Agent related. So it depends on your scenario and the needs you want to
meet.
> Rick, does this answer your question? If there is anything more I can do
to assist you, please
> feel free to post it in the group.
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>