SQL help - Discussion Forum - The Massassi Temple Forums Archive

This is the static archive of the Massassi Forums. The forums are closed indefinitely. Thanks for all the memories!

You can also download Super Old Archived Message Boards from when Massassi first started.

"View" counts are as of the day the forums were archived, and will no longer increase.

SQL help

2007-12-07, 2:06 PM #1

zanardi

Dictator for Life

Posts: 6,408

I need to know if something is possible.. here is my query

SELECT allfieldsIneed FROM #__la_auction ORDER BY published DESC, finish_date ASC

(#__ is just for the prefix.. those that are familiar with joomla will recognize it)

Ok the published field tells me if the auction is finished or still active (1,0) 1=active 0=finished..

I want all my published auctions on top. I want them sorted by finish_date ASC. (Which is exactly what it does) EXCEPT..

For all the non-published (0) auctions I want the finish_date to sort DESC.. is this possible in 1 query?

Oh and BTW I'm using mysql with php..

"Nulla tenaci invia est via"

2007-12-07, 2:32 PM #2

..would like a shrubbery.

Posts: 4,822

I've not got a dev environment to test with but it's possible by getting the table twice (though maybe there's a better way)
Something along the lines of....

Code:

SELECT fields
FROM #__la_auction AS activeAuctions
Inner Join #__la_auction AS finishedAuctions ON activeAuctions.id = finishedAuctions.id
WHERE activeAuctions.published = 0 
AND finishedAuctions.published = 1
ORDER BY activeAuctions.published DESC, activeAuctions.finish_date DESC, finishedAuctions.published DESC, finishedAuctions.finish_date DESC

Pretty sure the join condition isn't correct there but the idea is right. You may even be able to do it by selecting the table twice. You'd probably need to do a SELECT DISTINCT if you try that.

There may be a simpler way i'm overlooking.

TheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWho
SaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTh
eJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSa
ysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJ
kWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSays
NiTheJkWhoSaysNiTheJkWhoSaysNiTheJkWhoSaysNiTheJkW

2007-12-07, 2:35 PM #3

zanardi

Dictator for Life

Posts: 6,408

Hm I like that theory.. I'll play around with it in SQLyog see what I can come up with

"Nulla tenaci invia est via"

2007-12-07, 2:40 PM #4

Wolfy

I TYPE LOUD

Posts: 10,623

Originally posted by TheJkWhoSaysNi:

Ni's SQL

This would only work if the ID field is non-unique; otherwise, no rows will qualify.

As far as I know, MySQL doesn't support conditional ordering (nor do any of the popular RDBMS's). I can't see this being done is less than two queries. You're asking for basically two different data sets.

the idiot is the person who follows the idiot and your not following me your insulting me your following the path of a idiot so that makes you the idiot - LC Tusken

2007-12-07, 2:53 PM #5

..would like a shrubbery.

Posts: 4,822

Yes, as I said the join condition is wrong. (well, the WHERE clause will actually be the part that causes no records to be returned. The join itself should work. It will match each record to itself.

Maybe something like

Code:

SELECT DISTINCT allFields, 0-(published * UNIX_TIMESTAMP(activeAuctions.finish_date)) AS orderBy,  
FROM #__la_auction AS activeAuctions
LEFT OUTER JOIN #__la_auction AS finishedAuctions ON activeAuctions.id = finishedAuctions.id AND finishedAuctions.published =1
ORDER BY orderBy ASC,  finsihedAuctions.finishDate DESC

This will make the orderBy from the result 0 for non-published auctions and published ones will have a negative timestamp. The finished auctions are then ordered by the finish date descending.