Group Columns then Order By?

[Hilly_2004]

Is it possible?

 

Here is my table (Events)

 

 

Basically, I want to group the C_Timestamp, Timestamp, M_Timestamp_Edit, C_Timestamp_Edit and Timestamp_Edit as one and then order them based on the most recent (Descending).

 

My SELECT statement looks like so...

 

$query = "SELECT forum_members.M_FIRSTNAME, forum_members.M_SURNAME, Events.C_Timestamp, Events.Timestamp, Events.M_Timestamp_Edit, Events.C_Timestamp_Edit, Events.Timestamp_Edit FROM forum_members, Events WHERE forum_members.MEMBER_ID = Events.MEMBER_ID ORDER BY C_Timestamp DESC, Timestamp DESC, M_Timestamp_Edit DESC, C_Timestamp_Edit DESC, Timestamp_Edit DESC";

 

At the minute, it's just outputting information based on the order of the columns e.g. the first output is Timestamp, the second is M_Timestamp_Edit and the last being Timestamp_Edit.

 

I'm thinking that because the information in the table is a UNIX Timestamp, it might be better formatting the columns as DATE and inserting and sorting by that way - would there be any difference?

 

Thanks in advance for any help guys!

[Mchl]

I've no idea if it will work... but should. And it's ugly as hell, I know.

 

[added] It will not work (reults will be wrong) for some combinations of columns unfortunately. So yeah... it,s useless...

 

SELECT 
  forum_members.M_FIRSTNAME, 
  forum_members.M_SURNAME, 
  Events.C_Timestamp, 
  Events.Timestamp, 
  Events.M_Timestamp_Edit, 
  Events.C_Timestamp_Edit, 
  Events.Timestamp_Edit 
FROM 
  forum_members, 
  Events 
WHERE 
  forum_members.MEMBER_ID = Events.MEMBER_ID 
ORDER BY 
IF(
    IF(
      IF(C_Timestamp>Timestamp,C_Timestamp,Timestamp)>IF(M_Timestamp_Edit >C_Timestamp_Edit,M_Timestamp_Edit ,C_Timestamp_Edit),
      IF(C_Timestamp>Timestamp,C_Timestamp,Timestamp),
      IF(M_Timestamp_Edit >C_Timestamp_Edit,M_Timestamp_Edit ,C_Timestamp_Edit)
    ) > Timestamp_Edit,
    IF(
      IF(C_Timestamp>Timestamp,C_Timestamp,Timestamp)>IF(M_Timestamp_Edit >C_Timestamp_Edit,M_Timestamp_Edit ,C_Timestamp_Edit),
      IF(C_Timestamp>Timestamp,C_Timestamp,Timestamp),
      IF(M_Timestamp_Edit >C_Timestamp_Edit,M_Timestamp_Edit ,C_Timestamp_Edit)
    ),
    Timestamp_Edit
) DESC    

 

 

 

[Hilly_2004]

Although valid (bravo by the way!  ), unfortunately the output is still the same...

 

Timestamp, M_Timestamp_Edit and Timestamp_Edit.

 

Just seen your update!

 

Would it make any difference if I changed it from Timestamps to Date or would I stumble into the same problem?

 

[Mchl]

Datatype has nothing with it. It's just there is no MAX(a,b,...) function in MySQL

 

another idea

 

 

SELECT 
  forum_members.M_FIRSTNAME, 
  forum_members.M_SURNAME, 
  Events.C_Timestamp, 
  Events.Timestamp, 
  Events.M_Timestamp_Edit, 
  Events.C_Timestamp_Edit, 
  Events.Timestamp_Edit 
FROM 
  forum_members, 
  Events 
WHERE 
  forum_members.MEMBER_ID = Events.MEMBER_ID 
ORDER BY (
  SELECT ts FROM (
    SELECT C_Timestamp AS ts
    UNION
    SELECT Timestamp AS ts
    UNION
    SELECT M_Timestamp_Edit AS ts
    UNION
    SELECT C_Timestamp_Edit AS ts
    UNION
    SELECT Timestamp_Edit AS ts
  ) AS sq ORDER BY ts DESC LIMIT 1
) DESC    

 

Darn... it will not work either... But I almost have it

 

[edit]

 

Bleh... I have a working query, but it's so devilishly slow, I will not even show it here.

[Hilly_2004]

Datatype has nothing with it. It's just there is no MAX(a,b,...) function in MySQL

 

another idea

 

 

SELECT 
  forum_members.M_FIRSTNAME, 
  forum_members.M_SURNAME, 
  Events.C_Timestamp, 
  Events.Timestamp, 
  Events.M_Timestamp_Edit, 
  Events.C_Timestamp_Edit, 
  Events.Timestamp_Edit 
FROM 
  forum_members, 
  Events 
WHERE 
  forum_members.MEMBER_ID = Events.MEMBER_ID 
ORDER BY (
  SELECT ts FROM (
    SELECT C_Timestamp AS ts
    UNION
    SELECT Timestamp AS ts
    UNION
    SELECT M_Timestamp_Edit AS ts
    UNION
    SELECT C_Timestamp_Edit AS ts
    UNION
    SELECT Timestamp_Edit AS ts
  ) AS sq ORDER BY ts DESC LIMIT 1
) DESC    

 

Darn... it will not work either... But I almost have it

 

[edit]

 

Bleh... I have a working query, but it's so devilishly slow, I will not even show it here.

Ha! I honestly thought it would just have been a simple SELECT statement and I was being an idiot somewhere!

 

In terms of performance, I will only be bringing back 10 records, so hopefully if you have a working query it shouldn't effect performance that much?

[Mchl]

I think your best option is to create your own function like this (untested)

 

DELIMITER $$

DROP FUNCTION IF EXISTS `f_MAX5` $$
CREATE FUNCTION `f_MAX5` (IN1 TIMESTAMP, IN2 TIMESTAMP, IN3 TIMESTAMP, IN4 TIMESTAMP, IN5 TIMESTAMP) RETURNS TIMESTAMP
DETERMINISTIC
BEGIN

DECLARE m TIMESTAMP;

  SELECT MAX(ts) INTO m FROM (
  SELECT IN1
    UNION
    SELECT IN2
    UNION
  SELECT IN3
    UNION
  SELECT IN4
    UNION
  SELECT IN5
  ) AS sq;

RETURN m;

END $$

DELIMITER ;

 

And use it in your query like this:

 

SELECT
  forum_members.M_FIRSTNAME,
  forum_members.M_SURNAME,
  f_MAX5(Events.C_Timestamp,
  Events.Timestamp,
  Events.M_Timestamp_Edit,
  Events.C_Timestamp_Edit,
  Events.Timestamp_Edit ) AS ts
...

ORDER BY ts DESC

[/code]

[Hilly_2004]

I think your best option is to create your own function like this (untested)

 

DELIMITER $$

DROP FUNCTION IF EXISTS `f_MAX5` $$
CREATE FUNCTION `f_MAX5` (IN1 TIMESTAMP, IN2 TIMESTAMP, IN3 TIMESTAMP, IN4 TIMESTAMP, IN5 TIMESTAMP) RETURNS TIMESTAMP
DETERMINISTIC
BEGIN

DECLARE m TIMESTAMP;

  SELECT MAX(ts) INTO m FROM (
  SELECT IN1
    UNION
    SELECT IN2
    UNION
  SELECT IN3
    UNION
  SELECT IN4
    UNION
  SELECT IN5
  ) AS sq;

RETURN m;

END $$

DELIMITER ;

 

And use it in your query like this:

 

SELECT
  forum_members.M_FIRSTNAME,
  forum_members.M_SURNAME,
  f_MAX5(Events.C_Timestamp,
  Events.Timestamp,
  Events.M_Timestamp_Edit,
  Events.C_Timestamp_Edit,
  Events.Timestamp_Edit ) AS ts
...

ORDER BY ts DESC

[/code]

Firstly, thanks for all the help Mchl, it's very much appreciated.

 

However, can I do the above without the need for a MYSQL function? It's just I'm still on MYSQL 4 and I'm unable to upgrade in the mean time due to circumstances beyond my control.

[Mchl]

No idea.

[Mchl]

!!!

 

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest