Post reply

Select Query

  • May 10, 2005 at 8:29 am

    #92996

    Hi,

    I have a set of 1000 records in my table, I need to pick up every fourth record from the set of 1000 records ordering by date and display it to the user.

    Any help with this select query will be highly appreciated.

    Thanks in advance.

     

    Prasad Bhogadi
    www.inforaise.com

  • May 10, 2005 at 8:35 am

    #557798

    Do you have an ID or indexed field that's sequentially numbered ?

    what are the columns you have ?







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 10, 2005 at 8:37 am

    #557799

    I have ID field which is the PKEY of the table, I have varchar field containing text and I have one datetime field, the ordering should be done on the Datetime field.

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • May 10, 2005 at 9:14 am

    #557815

    Prasad - can't think of any other solution other than one with cursors in it...

    You would probably have to use a select top 4 * from table where ID = (select min(ID) from table) order by datetime - insert the first row in temp table - use the counter to move forward & "discard" next 3 rows etc..

    Maybe someone else will come up with something less intricate ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 10, 2005 at 9:41 am

    #557819

    Perhaps something like this :
     
    declare @t table ( id int identity(1,1) , fname varchar(20))
     
    insert into @t values ('a1')

    insert into @t values ('a2')

    insert into @t values ('a3')

    insert into @t values ('a4')

    insert into @t values ('b1')

    insert into @t values ('b2')

    insert into @t values ('b3')

    insert into @t values ('b4')

     
    select * from @t t1

    where [id]% 4=0

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • May 10, 2005 at 9:50 am

    #557821

    Dinakar - this assumes that all the IDs are sequential...if they aren't (which is usually the case in the real world) then [id] % 4 = 0 will not work...







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 10, 2005 at 10:02 am

    #557824

    then you can generate your own sequential id on the fly.
     
    Here's another sample :
     
    declare @t table ( id int  , fname varchar(20))
     
    insert into @t values (2,'a1')

    insert into @t values (4,'a2')

    insert into @t values (1,'a3')

    insert into @t values (6,'a4')

    insert into @t values (8,'b1')

    insert into @t values (9,'b2')

    insert into @t values (44,'b3')

    insert into @t values (78,'b4')

     
    create table #t ( seqid int, id int, fname varchar(20) )

    insert into #t (seqid, id, fname)

    SELECT

     (SELECT count(id) FROM @t x WHERE x.id <= y.id) AS [Sequence],

     id,

     fname

    FROM @t y

    --where [sequence]%4 = 0

    order by [id]

     
    select * from #t where seqid%4 = 0

    go

    drop table #t

    go

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • May 10, 2005 at 10:14 am

    #557829

    No, this isn't true. Dinar's solution is to bounce the records through a table variable with its own identify variable and this would be sequential.

    LinkedIn Profile

  • May 10, 2005 at 10:25 am

    #557832

    Yes - I see that with the second example....







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 10, 2005 at 10:34 am

    #557833

    Thanks for all the responses, I implemented a similar logic as posted by Dinakar and it worked fine.

    Regards

    Prasad Bhogadi
    www.inforaise.com

  • May 11, 2005 at 7:11 am

    #558054

    Same logic applied without using a temp table :

    --whole table

    Select id, name from dbo.SysObjects O1 order by id

    --1st, 5th rows....

    Select id, name from dbo.SysObjects O1 where (Select count(*) from dbo.SysObjects O2 where O2.id < O1.id) % 4 = 0 order by id

  • May 11, 2005 at 7:20 am

    #558059

    Remi - you're amazing!

    Haven't had time to compare execution plans yet (bet you already have) - but for sheer brevity and elegance your t-sql is outstanding!







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 11, 2005 at 7:30 am

    #558065

    I didn't test it but since there's no create table / insert then I assume it's gonna be faster... But then again 1000 rows is not that mayny.

  • May 11, 2005 at 7:34 am

    #558067

    Also keep in mind that this query uses the the clustered index for the order by (implied), you might have to tweak the indexes or the query to achieve the same results.

  • May 11, 2005 at 7:39 am

    #558068

    Wasn't the question how to get every 4th row?

    SELECT S1.id , S1.name

    FROM dbo.sysobjects AS S1, dbo.sysobjects AS S2

    WHERE S2.id <= S1.id

    GROUP BY S1.id, S1.name

    HAVING COUNT(*)%4=0

    ORDER BY S1.id

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply