sql expert query

What is your question?

sorry ,it was wrongly submit before the data .. updated it now.

It looks like you need consecutive "keys" on the rows to make the easiest query.

WITH td AS
(
  SELECT page, row_number (over page, order by page) id, title
)
SELECT t0.title, t1.title
FROM td t0
FULL JOIN td t1
  ON t0.id = t1.id - 1
ORDER BY t1.id;

Something like that should work....

What are the requirements that gets you form the sample data to the desired results?

How do you get: Fondue|Sandwich
and Tomato soup|NULL

You also asked this in SQL Server and MySQL.  Which database or both and what versions?

Hi Kent,

Throws syntax error, table name is book .
Do i need to add anything ? I am using mysql .

Thanks

Hi ,

Please find the version below. ( i am doing it via phpmyadmin)
mysql  Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Thanks

What are the rules for what row goes with what?

From the sample data and results, I don't understand how to pull the rows together.

Oops...  The inner sub-query needs the name of your table.

WITH td AS
(
  SELECT page, row_number (over page, order by page) id, title FROM book
)
SELECT t0.title, t1.title
FROM td t0
FULL JOIN td t1
  ON t0.id = t1.id - 1
ORDER BY t1.id;

Hi Kent,

For some reason , its not working .

Error

Static analysis:
1 errors were found during analysis.

1. Unrecognized statement type. (near "WITH" at position 0)

SQL query:
WITH td AS ( SELECT page, row_number (over page, order by page) id, title FROM book ) SELECT t0.title, t1.title FROM td t0 FULL JOIN td t1 ON t0.id = t1.id - 1 ORDER BY t1.id
MySQL said: 
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over page, order by page) id, title FROM book
)
SELECT t0.title, t...' at line 3

Hi Slightwv,

The table contains the page number and the title.  The page number starts from 0 which is missing there and also the page number 5 is missing.

we need to print the page in sequence. Hope im clear now.

Thanks

Try this:

with cte as (
	select
		floor(page/2) page_group,
		case when page%2 = 0 then title end title1,
		case when page%2 = 1 then title end title2
	from book
)
select
	max(title1),
	max(title2)
from cte group by page_group
order by page_group
;

Select allOpen in new window

My test fiddle:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=e7a3b40a6d5a2a2bbdfd1af8d4319c88

Weird.  There's an extraneous comma, but that shouldn't result in that error.

WITH td AS ( SELECT page, row_number (over page order by page) id, title FROM book ) SELECT t0.title, t1.title FROM td t0 FULL JOIN td t1 ON t0.id = t1.id - 1 ORDER BY t1.id

Still that query won't do quite what you want.  It looks like every item is to be listed in the result once.  Whether an item occurs on the left or right depends on whether it's paired with the item immediately above, and whether it's in the even or odd position relative to the last item that wasn't paired with a predecessor.

I'd likely use a recursive query to solve that.  It seems like the cleanest way to resolve the "what ifs".

Hi Slightwv,

The solution is perfect . I checked in fiddle .
But i am getting pop up  "500 internal server error" in phpmyadmin.
May be something wrong with my phpmyadmin, i will try reinstall and let you know.

Thanks

Have to give columns a name....

select
   max(title1) lhs,
   max(title2) rhs
from (
   select
      floor(page/2) page_group,
      case when page%2 = 0 then title end title1,
      case when page%2 = 1 then title end title2
   from book
) x
group by page_group
order by page_group
;

Select allOpen in new window

Very clever @slightwv, very lateral thinking. Well done.

Change "lhs" and "rhs" in the query to the names that you want.

slightwv's  solution got me to thinking.

My initial approach was going to be the good old fashioned Tally table and the magic of inline variables. Still on an earlier version of MySQL so cannot do the recursive CTE.

Having trouble with linking and keeping sequences, so, ended up doing the unthinkable lookup of title as part of the select. Big overhead, so not so good for a few thousand pages.  For a few dozen, it seems to work.

The reason for the Tally table (a table or view of numbers), was a thought about missing pages in the feed.

So, created some test data like :

drop table if exists EE_book;

create table EE_book(`page` int, title varchar(20));

insert EE_book (`page`, title) values
(1,'Scrambled eggs'),
(2,'Fondue'),
(3,'Sandwich'),
(4,'Tomato soup'),
(6,'Liver'),
(10,'steak'),
(13,'flour');
 

Select allOpen in new window

And the (ugly) code

select pageno, max(IF(side = 1, title,NULL)) as lhs,max(IF(side = 2, title,NULL)) as rhs
from ( select n,@page := IF(@side>1 , @page + 1, @page) AS pageno,  @side:=if(@side > 1, 1, @side+1) as side
         ,(select title from EE_book b where b.`page`=t.n) as title
        from (select n from tally) t,(select max(`page`) as maxpage, @page:=0,@side:=0 from book) v
       where n <=maxpage) s
group by pageno;

Select allOpen in new window

And the desired results :

Note how pageno = 4 is blank. I couldnt replicate any other way than using a tally table.

Hi Mark,

I added data to my test, as you did, and came up with similar results.  It made me realize that we still have a question about the data, that is "based on the original data, items could appear in the left or right column based on page number or by whether the item has a item has a predecessor item on the previous page".  I originally read it as the latter, but in the context of book and page, the former does make more sense.

Recursive CTEs appear to be around since MariaDb 10.2.2.
https://mariadb.com/kb/en/mariadb-1022-release-notes/

Interesting idea if you need 'blank' rows for gaps in pages.

Get's even more interesting if you have a row:  (999,'mangos')

@magento,
Do you need 'blank' entries for gaps?

If so, we can work on something.  Possibly without a tally table if the MariaDb docs are correct and you can use recursive CTEs or older methods to generate a series.

@Kent,

yeah, I reckon the former does make more sense too. It was crystallized when pondering slightwv's query, because others thought that way too.

@slightwv,

Dont laugh, I had one like that, but mango's didnt fit the recipe for steak and liver fondue in Tomato sauce with bread. Still working out scrambled eggs.

This is potentially not as straight forward as once considered. I still like a lot your solution in https://www.experts-exchange.com/questions/29211512/sql-expert-query.html#a43262733 and if you had the column names, would have said it was the solution..... in fact, I marked it as such, then changed my mind and endorsed instead....

I wouldn't think simple column aliases would cause a phpadmin error.

If "missing" groups are needed, I'm sure there are cleaner ways without accessing the base table more than once and better use of the 'tally' table.

Until magento confirms the missing groups are necessary, I'm not going to spend the time trying.

Hi Slightwv,

No , the one you shared is working fine post the adjustments done by Mark .



Thanks

If you don't need anything else, please don't forget to check 'This is the solution' and 'This is helpful' on the post or posts that helped you answer your question.

sure, will do .
I thought the thread was live and some discussions happening hence waited . Will close it accordingly.

>>adjustments done by Mark .

@magento,

I see you didn't reward Mark for fixing something that I would have never figured out:  Column aliases.

*laughing* thanks @slightwv, but not necessary. Your words are enough :)

Mark , sorry for that . I thought the idea is from  Slightwv hence i gave full points to him .
Between the expected output was already shared by him in the fiddle . ofcourse, the solution you shared is really helpful .

Hi Magento,

No worries. No need for apologies.

I am just getting back into answering questions, and it does feel to be a bit different. It seems that any appreciation is only shown through points. The simple acknowledgement and thanks, doesnt quite fit with the points economy anymore.

As I said above in https://www.experts-exchange.com/questions/29211512/sql-expert-query.html#a43264516 "Your words are enough :)". But that now is an old concept, made redundant. Seems to be acknowledged by points alone. Go figure.

I am a believer that there should only ever be one "Answer" and it was slightwv. Some threads, it might not be as obvious because it inspired the Author to their own solution. Whereby they (ideally) post their answer,accept that, then go through and distribute points accordingly.

Moral of the story ? Always take the time to say "Thanks" and (in the points economy) recognise helpful posts. Which is available for Author and Expert alike. And for me, I simply have to get used to the new way of interacting, or not.

So, thank you for getting back, it is encouraging :)

Regards,
Mark

To add:
Mark filled in the missing piece I would have never gotten.

As I mentioned in a previous post:  You can select as many "Helpful" and "Solution" comments as necessary.

Where I do disagree with Mark:  There can be multiple solutions.  That is what makes Experts-Exchange more of a team effort.  Experts collectively bring individual talents together to solve individual problems.  Since, no one knows everything.