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?

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.

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

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

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.

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

@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.

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?

Is there anyone who knows how I could turn this into a view or stored procedure?

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%

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

?

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)

okay, well, let's just merge the threads, shall we?

otherwise things will just get too complicated