Help with SQL to sort flexinodes alphabetically by a given field

By matt@antinomia on 8 Feb 2006 at 21:33 UTC

Hey all, I was wondering if somebody could give me a quick hand with writing a tricky (at least, for me) SQL query.

I'm using flexinode as an address directory, and I have defined field 80 as 'Last Name'. Trouble is, I'm not sure how to write the SQL to fetch the results alphabetically, as there is no SQL field last_name. Somehow, I need to ORDER BY flexinode_data.textual_data WHERE flexinode_field.field_id = 80 (I think).

This is the best I could come up with:

SELECT n.nid, n.created FROM {node} n, {flexinode_data} o WHERE n.type = 'flexinode-1' AND n.status = 1 AND n.nid = o.nid AND o.field_id = '58' AND o.textual_data LIKE '%1%' ORDER BY flexinode_data.textual_data WHERE flexinode_field.field_id = 80");

...but that doesn't work.

How do I sort flexinode data by a given field using a single SQL statement? Am I on the right track?

Thanks,
Matt

Comments

Does it return data at all?

kerrizor commented 9 February 2006 at 02:52

Does it return data at all? I've usually seen this done like this:

SELECT [stuff] FROM [table] WHERE [conditions] ORDER BY [field] [ASC|DESC]

Adding the second WHERE clause is useless.

Really, Google is your friend. Honest.

Believe you me, I've Googled

matt@antinomia commented 9 February 2006 at 18:20

Believe you me, I've Googled and Googled and Googled. Google is my best friend. Maybe I didn't describe the issue well enough in my original post...

First, I want the query to match records where o.field_id = '58' and o.textual_data LIKE '%1%' (which takes records from a particular category (i.e. Student) and leaves the rest (i.e. Teacher, Staff).

Secondly, the query should sort records by o.textual_data where o.field_id = '58' (which is the last name field).

It's not a matter of a simple SQL statement as you pointed out above. I can pull it off using two statements, but I'd liike to combine them if possible for efficiency.

Thanks again,
Matt

I think it is actually

kerrizor commented 9 February 2006 at 21:37

I think it is actually pretty simple, if you think about it ni terms of compound statements.

That is, you can make the first part work, right? That's the first step - get your compound WHERE phrasing working first.. then to add the ORDER BY. The error (I believe) was in how you were welding ORDER BY onto the original statement, since from your description it seems to me that you're missing the proper syntax of ordering.

sort records by o.textual_data where o.field_id = '58'

This is the part that I mean.. you've already fetched the data "WHERE o.field_id = '58'" so you just need "ORDER BY o.textual_data DESC"

If I'm misunderestimating you, let me know; I get the feeling that your brief description of the problem is leaving out some piece of information or otherwise clouding the problem.