relatively advanced SQL statment help please??!! :P

[alphanumetrix]

Okay, so I have two tables... "series" & "links"

 

Series Table looks like: id, name

 

The links table looks like: id, placement, date, series

 

Now I'm basically trying to get the latest links (latest means the highest "placement") for EACH series in order of the DATE of the link... What I've come up with is this, but it's not working at all:

 

"SELECT MAX( placement ) AS max_placement, max( date ) AS max_date
FROM links
GROUP BY series
ORDER BY max_date DESC
LIMIT 0 , 30"

[Maq]

Can you dump the entire table structure for each one and post it?

[alphanumetrix]

Yeah, no problem. I tried to keep it simple in my description above, because the design is a little more complex in actuality, because of the purpose of the website.

 

Here is a dump of the two tables (not data actually added--i would, but the database has in excess of 10 thousand, so you can see why i didn't... & it's actually data that is not meant to be disclosed publicly):

 

-- phpMyAdmin SQL Dump
-- version 3.1.5
-- http://www.phpmyadmin.net
--
-- Host: mysql1026.servage.net
-- Generation Time: Dec 30, 2009 at 08:49 PM
-- Server version: 5.0.85
-- PHP Version: 5.2.42-servage13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bliljerk10121`
--

-- --------------------------------------------------------

--
-- Table structure for table `links`
--

CREATE TABLE IF NOT EXISTS `links` (
  `id` int(6) NOT NULL auto_increment,
  `placement` int(10) NOT NULL,
  `owner` int(10) NOT NULL,
  `date` text NOT NULL,
  `type` varchar(255) NOT NULL,
  `ext` varchar(255) NOT NULL,
  `size` int(6) NOT NULL default '0',
  `quality` varchar(120) NOT NULL default 'N/A',
  `link` text NOT NULL,
  `hits` int(10) NOT NULL default '0',
  `series` int(6) NOT NULL,
  `episode` varchar(50) NOT NULL,
  `details` text NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `type` (`type`,`series`,`episode`),
  KEY `ext` (`ext`),
  KEY `size` (`size`),
  KEY `owner` (`owner`),
  KEY `quality` (`quality`),
  KEY `hits` (`hits`),
  KEY `placement` (`placement`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10146 ;

-- --------------------------------------------------------

--
-- Table structure for table `series`
--

CREATE TABLE IF NOT EXISTS `series` (
  `id` int(6) NOT NULL auto_increment,
  `date` varchar(255) NOT NULL,
  `published` int(10) NOT NULL default '1',
  `name` varchar(255) NOT NULL,
  `site` varchar(255) NOT NULL,
  `picture` text NOT NULL,
  `status` varchar(125) NOT NULL,
  `description` text NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `site` (`site`),
  KEY `date` (`date`),
  KEY `name` (`name`),
  KEY `published` (`published`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=315 ;

[alphanumetrix]

No one can help me with this??? 

 

I know of several alternatives to this problem, but I would MUCH rather do it with just this SQL statement. 

[JustLikeIcarus]

The following should return the series col, date col and the highest placement for eachset of series, date.

Is that what your wanting?

select series, date, max(placement)
from links
group by series, date
order by series, date

[alphanumetrix]

The following should return the series col, date col and the highest placement for eachset of series, date.

Is that what your wanting?

select series, date, max(placement)
from links
group by series, date
order by series, date

 

Nope, that's not what I'm looking for. I'm looking for the highest Placement of EACH series ordered by date.

[JustLikeIcarus]

Could you give an example of how the end result should look. The query I posted would output something like

 

series A, 12/30/2009, 3

series A, 12/29/2009, 10

etc...

 

I guess im just confused on what your end result should look like.

[Maq]

The following should return the series col, date col and the highest placement for eachset of series, date.

Is that what your wanting?

select series, date, max(placement)
from links
group by series, date
order by series, date

 

Nope, that's not what I'm looking for. I'm looking for the highest Placement of EACH series ordered by date.

 

This will get you the highest placement for each series from the most recent date of each series.

 

SELECT MAX(placement) FROM links GROUP BY series ORDER BY `date` DESC;

[JustLikeIcarus]

 

This will get you the highest placement for each series from the most recent date of each series.

 

SELECT MAX(placement) FROM links GROUP BY series ORDER BY `date` DESC;

 

I may be wrong but this query will only give you the max placement for each series regardless of date.  This is because the ORDER BY clause isnt even used untill after the grouping is performed.  Order of operations is as follows

  1. FROM clause

  2. WHERE clause

  3. GROUP BY clause

  4. HAVING clause

  5. SELECT clause

  6. ORDER BY clause

So if you want date to affect the grouping it needs to be included in the group by.

[alphanumetrix]

This will get you the highest placement for each series from the most recent date of each series.

 

SELECT MAX(placement) FROM links GROUP BY series ORDER BY `date` DESC;

 

This code isn't exactly right... I need the ENTIRE row WHERE the max placement is, and the results have to be DISTINCT based on the SERIES... So I don't want any duplicate series...I basically just want the ENTIRE ROW where HIGHEST placement is from EACH series and order them by date.. Make more sense?

 

The code you had gave me a result like this:

 

max(placement)

 

12

13

13

13

24

13

25

45

26

25

 

The result I'm looking for is going to be like this:

 

Series

Placement

date

 

314 12 December 30, 2009, 9:04 am

 

313 13 December 30, 2009, 9:02 am

 

312 13 December 30, 2009, 9:02 am

 

309 13 December 30, 2009, 8:59 am

 

306 24 December 26, 2009, 6:01 pm

 

307 13 December 26, 2009, 5:59 pm

 

308 25 December 26, 2009, 5:58 pm

 

303 45 December 26, 2009, 4:13 pm

 

41 90 December 26, 2009, 4:06 pm

 

[alphanumetrix]

notice how there are no duplicate series & it shows the maximum placement for each of those series, as well as it is ordered by date

[fenway]

You'll need to join it back after you determine the max.

[alphanumetrix]

Um... I never really figured out a solution using just SQL, but I re-did it with some PHP, and a modified table, now I have the same desired result. Since no one seems to know how to figure this out, I'll just close this as solved.