"Gleep" <Gl***@Gleep.com> wrote in message
news:ut********************************@4ax.com...
my table is like this
example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1
price2 price3 (etc..)
What everyone else said: Your design is wrong. If it were correct, you could
do this with a simple
SELECT id, name, username, outcome, max(date)
FROM table1 JOIN table2 ON table2.ID = table1.ID
GROUP BY ID, name, username, outcome
Since you don't seem to accept the fact that your table design is a Bad
Thing That Needs To Be Fixed:
If you're not using MySQL 4.1, you could try something like this:
SELECT ID,
CASE
WHEN Date1 > Date2 && Date1 > Date3 && Date1 > Date4 && Date1 > Date5 THEN
Date1
WHEN Date2 > Date1 && Date2 > Date3 && Date2 > Date4 && Date2 > Date5 THEN
Date2
WHEN Date3 > Date1 && Date3 > Date2 && Date3 > Date4 && Date3 > Date5 THEN
Date3
WHEN Date4 > Date1 && Date4 > Date2 && Date4 > Date3 && Date4 > Date5 THEN
Date4
WHEN Date5 > Date1 && Date5 > Date2 && Date5 > Date3 && Date5 > Date4 THEN
Date5
END AS MaxDate
FROM YuckyTable
I just tested this on a table with five date columns and it works correctly.
I'll leave it to you to extend it to 15 date columns.
If you're using MySQL 4.1, you could use a subquery along these lines:
(Note: I'm writing this as I would write it for MS-SQL Server, although I
wouldn't write it this way for MS-SQL Server since I wouldn't design the
tables this way. Translation: This is untested and may have errors.)
SELECT id, name, username, outcome, max(date)
FROM table JOIN
(
SELECT id, date1 FROM table
UNION
SELECT id, date2 FROM table
UNION
SELECT id, date3 FROM table
UNION
SELECT id, date4 FROM table
UNION
SELECT id, date5 FROM table
UNION
SELECT id, date6 FROM table
UNION
SELECT id, date7 FROM table
UNION
SELECT id, date8 FROM table
UNION
SELECT id, date9 FROM table
UNION
SELECT id, date10 FROM table
UNION
SELECT id, date11 FROM table
UNION
SELECT id, date12 FROM table
UNION
SELECT id, date13 FROM table
UNION
SELECT id, date14 FROM table
UNION
SELECT id, date15 FROM table
) table2 ON table2.ID = table.ID
GROUP BY ID, name, username, outcome
Steve
--
Steven C. Gallafent - The Computer Guy, Inc.
st***@compguy.com -
http://www.compguy.com/