Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2006
    Posts
    19

    Unanswered: Advanced Query, select every 2nd row

    Hello,

    I want to select from a table every second row (don't ask me why :P).
    So 1,3,5,7,.. or 2,4,6,8,.....

    I am trying different ways but cant get it working in one line of code.

    This code does work when ran from phpmyadmin:

    set @a:=0;
    select @a:=@a+1,mod(@a,2),ordr_ID, poft_Sub_Month from er_poft
    group by 3,4 having mod(@a,2) = 0


    The problem is that the application does not accept this. So is there a way to turn this into a view or something or make it into 1 line of code? I was trying to fix it with a Case statement but the @a variable always stays NULL then and in the query I can't set it to a number. Anyone any idea how to fix this?
    Last edited by jacsoft; 03-16-08 at 10:23. Reason: additional info

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you have something that uniquely identifies every other row, say a contiguous number, in which case dividing by two an looking for the remainder to be either 0.5 or 0 would be one way.

  3. #3
    Join Date
    Jan 2006
    Posts
    19
    Yes I have something unique (a Primary Key as auto_increment) only the problem is that I cant use that field. The primary key is "at random" and not the correct value to sort on. The problem is that I have to sort the table ascending first. And I need the second row of the sorted table. Then the primary key cannot be used anymore. Also not when items get deleted from the row. I'll show a small example:

    Table:
    Key Field
    1 Test
    2 Bar
    3 Foo
    4 Dar

    Now this get sorted on the field:
    Key Field
    2 Bar
    4 Dar
    3 Foo
    1 Test

    You see now I cant use the key anymore, the results I want are now:

    Key Field
    2 Bar
    3 Foo

    Key Field
    4 Dar
    1 Test

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So unless you can assign an incrementing value as part of the query...
    the only other way I can see would be to use a temporary table, possible but messy
    or read all the records and bin every other record.. not nice

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It is possible to create a counted number association, however I don't guarantee the method as I believe it to be rather "hacky". It relies on a JOINed table and also MySQL performing things in a particular order (i.e. working out @a) and also using a select on a derived query.
    Code:
    SELECT * 
    FROM 
     (
      SELECT @a:=@a+1 as 'a'
          ,ordr_ID
          ,poft_Sub_Month 
      FROM er_poft
      JOIN (SELECT @a:=0) x
     ) y
    WHERE mod(a,2) = 0;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jacsoft
    I want to select from a table every second row (don't ask me why :P).
    okay, why?

    no, srsly

    because a good place to do this type of logic is in the application layer -- just retrieve the table in sorted order and ignore every other row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2006
    Posts
    19
    @Aschk Thank you! That did the trick! Stupid I haven't thought of it myself.... But thanks!

    @r937. In know it should be in the application layer, but here is the deal. I am making an Ajax application and using a freeware report manager to build the reports from the MySQL database. The problem is that I can't edit that code and it takes way to much time to make it myself. That's why.

    The report should be two columned from the table, but splitting the table and joining in a view makes it hard(er) to edit the data with the Ajax application. Unfortunately the report manager doesn't have the capability to do multi columns from 1 table.

    But Aschk fixed it. Thanks everyone for helping.

  8. #8
    Join Date
    Jan 2006
    Posts
    19
    Still a small question:

    My Query is now in total:

    SELECT * FROM `telefoonnummers`
    WHERE `Afdeling` IN
    (SELECT `Afdeling`
    FROM
    (
    SELECT @a:=@a+1 as 'a'
    ,`Afdeling`
    FROM `Afdeling-telefoon`
    JOIN (SELECT @a:=0) x
    ORDER BY `Afdeling`
    ) y WHERE mod(a,2) = 0) ORDER BY `Afdeling`, `Naam`

    This works. The only problem is that I now want to make a view out of it. But it says it is not possible. So I add: CREATE VIEW test as

    But it returns:
    #1349 - View's SELECT contains a subquery in the FROM clause

    Is it not possible to turn this into a view?

  9. #9
    Join Date
    Jan 2006
    Posts
    19
    Is there anyone who knows how I could turn this into a view or stored procedure?

  10. #10
    Join Date
    Jan 2006
    Posts
    19

    [MySQL] Select 50% of the rows

    Hello,

    Is there a way to select 50% of the rows of a table with a simple query of view?

    SELECT * FROM test LIMIT 0,50%

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is simple if you know how many rows there are

    to find out how many rows there are, you could run this --

    SELECT COUNT(*) FROM test

    take the result, divide it by 2, and then you can run this --

    SELECT * FROM test LIMIT 0,N -- where N is rows/2


    of course, LIMIT without ORDER BY is kind of pointless, yes?




    by the way, are you going to ask how to turn this into a view like you did here --http://www.dbforums.com/showthread.php?t=1628292

    ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2006
    Posts
    19
    Well the proposed solution is very elementary and simple. But this does not solve the problem. I need like 1 query which immediately solves this problem
    I do not know the number of rows at the moment that I need the results.
    Limit is not pointless without an order by in my opinion, but most of the time it might be smart to do an order by indeed.

    I need it in an external application and so therefore I can not do 2 queries.
    I was trying something like:

    SELECT *
    FROM TABLE
    LIMIT 0, (SELECT Round(Count(*)/2) FROM TABLE)

    OR

    SELECT *
    FROM TABLE
    WHERE ROWNUM <= (SELECT Round(Count(*)/2) FROM TABLE)



    (ps: Yes it is an other idea to solve the problem in the other thread)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, well, let's just merge the threads, shall we?

    otherwise things will just get too complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •