Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    1

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

    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);

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    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.
    Last edited by guelphdad; 02-17-05 at 01:01.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    Last edited by r937; 02-18-05 at 08:57.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Synpax, i'm waiting........

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

  5. #5
    Join Date
    Mar 2004
    Posts
    480

    Wink

    I dunno Rudy, I think synpax definitely said "one line select query" , I think your answer was spread over multiple lines.

Posting Permissions

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