Will Paypal you $20 to write this one line select query

Synpax · #1 (**permalink**) 02-16-05, 17:26

I will paypal you $20 to write this single line of query.

I have a MySQL table that I am querying. The query is quiet complicated so I was wondering if one of you brilliant fellows could help out someone new.

I tried looking through the documentation for this - and normally the documentation is very helpful, but while I am sure that this select can be done i a single query, I'm at a loss as to how to write it.

I am selecting all of the fields in the table known as content. Where the rub is is in the sort.

An example would be to "Select * from content where parent = 'press' order by (you fill in this blank)"

There are three fields involved in the sort. They are priority, pubdate, and publisher_TS.

The priority field has numbers 0-9 in it. The pubdate has an eight digit date (YYYYMMDD) or may be blank. The publisher_TS has a full timestamp in it (YYYYMMDDHHMMSS).

First I want priorities sorted from 1 to 9 excluding anything with a 0 priority. Then I want pubdate and publisher_TS sorted together - if a record has a pubdate entered, pad it out to the left with six zeroes and sort using that. If it doesn't have a pubdate, go ahead and use the publish_TS.

Is this possible to do with one query?

---

More Explanation:

This is a table that controls news stories. THe query is determining what order they show up on a web page.

Normally, they show up in order of the publisher_ts (timestamp). There are two over-rides to this.
1) Pubdate. By entering a pubdate, they can specify the date that the story appears and what it's order is regardless of the publisher_ts. Normally this is empty.
2) Priority. If they want to force a story to be in the top position, the can enter a 1-9. This way, no matter how many new stories are posted, these priority ranked records will get pulled first. Give three stories the priorities 1,2 and 3, and they will be the top 3 stories pulled.

----

Table Design:
CREATE TABLE `content` (
`uniquename` varchar(10) NOT NULL default '',
`parent` varchar(10) NOT NULL default '',
`creator` varchar(10) NOT NULL default '',
`creator_ts` timestamp(14) NOT NULL,
`editor` varchar(10) NOT NULL default '',
`editor_ts` timestamp(14) NOT NULL default '00000000000000',
`publisher` varchar(10) NOT NULL default '',
`publisher_ts` timestamp(14) NOT NULL default '00000000000000',
`draft` char(1) NOT NULL default '',
`publish` char(1) NOT NULL default '',
`priority` tinyint(4) NOT NULL default '0',
`title` varchar(100) NOT NULL default '',
`subtitle` varchar(100) NOT NULL default '',
`credit` varchar(100) NOT NULL default '',
`graphic_s` varchar(100) NOT NULL default '',
`graphic_l` varchar(100) NOT NULL default '',
`graphic_text` varchar(100) NOT NULL default '',
`previewgraph` longblob NOT NULL,
`body` longblob NOT NULL,
`pubdate` int(8) NOT NULL default '0'
) TYPE=MyISAM;

----

Sample Data:

INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('home', '', '', '20050215225428', 'braynard', '20050215225428', 'braynard', '20050215225428', '', '1', 0, 'Home', 'Welcome to the Media Freedom Project''s Website', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('aboutus', 'home', '', '20050215225055', 'braynard', '20050215225055', 'braynard', '20050215225055', '', '1', 0, 'About Us', 'Learn More About The Media Freedom Project', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press', 'home', '', '20050215225122', 'braynard', '20050215225122', 'braynard', '20050215225122', '', '1', 0, 'Press', 'Get the Latest News on Media Issues', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('join', 'home', '', '20050215225136', 'braynard', '20050215225136', 'braynard', '20050215225136', '', '1', 0, 'Join Us', 'Join Our Organization', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('contactus', 'home', '', '20050215225322', 'braynard', '20050215225322', 'braynard', '20050215225322', '', '1', 0, 'Contact Us', 'Get in Touch with The Media Freedom Project''s Staff', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('contribute', 'home', '', '20050215225402', 'braynard', '20050215225402', 'braynard', '20050215225402', '', '1', 0, 'Contribute', 'Support the Cause', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press01', 'press', 'braynard', '20050216170243', '', '00000000000000', '', '20050216170243', '', '1', 1, 'Press 01', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press02', 'press', 'braynard', '20050216170300', '', '00000000000000', '', '20050216170300', '', '1', 2, 'Press 02', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press03', 'press', 'braynard', '20050216170318', '', '00000000000000', '', '20050216170318', '', '1', 3, 'Press 03', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press04', 'press', 'braynard', '20050216170620', '', '00000000000000', '', '20050216170620', '', '1', 0, 'Press 04', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press05', 'press', 'braynard', '20050216170937', 'braynard', '20050216170937', 'braynard', '20050216170937', '', '1', 0, 'Press 05 with pubdate January 1', '', '', '', '', '', '', '', 20050101);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press06', 'press', 'braynard', '20050216170946', 'braynard', '20050216170946', 'braynard', '20050216170946', '', '1', 0, 'Press 06', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press07', 'press', 'braynard', '20050216170952', 'braynard', '20050216170952', 'braynard', '20050216170952', '', '1', 0, 'Press 07', '', '', '', '', '', '', '', 0);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('Press09', 'press', 'braynard', '20050216170838', '', '00000000000000', '', '20050216170838', '', '1', 0, 'Press 09 with March 30 Pub Date', '', '', '', '', '', '', '', 20050330);
INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press08', 'press', 'braynard', '20050216170903', '', '00000000000000', '', '20050216170903', '', '1', 4, 'Press 08', '', '', '', '', '', '', '', 0);

guelphdad · #2 (**permalink**) 02-16-05, 23:56

Quote:

First I want priorities sorted from 1 to 9 excluding anything with a 0 priority. Then I want pubdate and publisher_TS sorted together - if a record has a pubdate entered, pad it out to the left with six zeroes and sort using that. If it doesn't have a pubdate, go ahead and use the publish_TS.

Do you mean that after the priority column you next want rows where pubdate has a value and then after the priority ones, the pubdate ones, then order the rest of the values according to publish_TS?

try this:

Code:

select * from content
order by
(case when priority = 0 then 1 else 0 end),
(case when pubdate = 0 then 1 else 0 end),
publish_TS

This places any row with a priority of non zero at the top in ascending order, then the rows without a priority but with a pubdate next in ascending order, and lastly the rows without a priority and without a pubdate in ascending order.

r937 · #3 (**permalink**) 02-17-05, 00:38

your first ORDER BY column is quite clever, guelphdad, so i'll borrow it

the rest of it, though, is off a wee bit

Code:

INSERT INTO content (uniquename, parent, creator
, creator_ts, editor, editor_ts, publisher, publisher_ts
, draft, publish, priority, title, subtitle, credit
, graphic_s, graphic_l, graphic_text, previewgraph, body, pubdate) 
VALUES ('Press09A', 'press', 'braynard'
, '20050216170838', '', '00000000000000', '', '20050216170838'
, '', '1', 0, 'Press 09 with March 30 Pub Date', '', ''
, '', '', '', '', '', 20050216);

select uniquename,priority,publisher_ts,pubdate
 from content
order by
 [ will be revealed upon hearing back from Synpax ]

uniquename,priority,publisher_ts,pubdate
press01    1    20050216170243    0
press02    2    20050216170300    0
press03    3    20050216170318    0
press08    4    20050216170903    0
press05    0    20050216170937    20050101
aboutus    0    20050215225055    0
press      0    20050215225122    0
join       0    20050215225136    0
contactus  0    20050215225322    0
contribute 0    20050215225402    0
home       0    20050215225428    0
Press09A   0    20050216170838    20050216
press04    0    20050216170620    0
press06    0    20050216170946    0
press07    0    20050216170952    0
Press09    0    20050216170838    20050330

FYI, Synpax, you can make the paypal payment here --> http://r937.com/payments.cfm