Re: complicated query | no Sub query

[Anoop kumar V]

My profound apologies

here is the table create structure.
the biggest problem i think is that this table does not have any primary 
keys or atleast unique columns: (I think joins require unique columns)

mysql> show create table isr2_aud_log\G
*** 1. row ***
Table: isr2_aud_log
Create Table: CREATE TABLE `isr2_aud_log` (
`id_secr_rqst` varchar(64) NOT NULL default '',
`dt_aud_rec` datetime NOT NULL default '-00-00 00:00:00',
`name_rec_type` varchar(30) default NULL,
`cd_rqst_type` varchar(15) default NULL,
`id_user` varchar(10) default NULL,
`name_user_first` varchar(40) default NULL,
`name_user_mid` varchar(40) default NULL,
`name_user_lst` varchar(40) default NULL,
`cd_user_div` varchar(10) default NULL,
`cd_user_cst_cntr` varchar(15) default NULL,
`id_actnee` varchar(10) default NULL,
`name_actnee_first` varchar(40) default NULL,
`name_actnee_mid` varchar(40) default NULL,
`name_actnee_lst` varchar(40) default NULL,
`cd_pltfrm` varchar(10) default NULL,
`cd_rsrc_sub_type` varchar(10) default NULL,
`cd_actn` varchar(10) default NULL,
`cd_rsrc_div` varchar(10) default NULL,
`name_grp` varchar(70) default NULL,
`name_svr` varchar(70) default NULL,
`name_rsrc_1` varchar(70) default NULL,
`name_rsrc_2` varchar(70) default NULL,
`name_rsrc_3` varchar(70) default NULL,
`name_rsrc_4` varchar(70) default NULL,
`name_rsrc_5` varchar(70) default NULL,
`cd_sts_apprl` varchar(30) default NULL,
`cd_prcsg_type` varchar(10) default NULL,
`text_actnee_cmnts` varchar(255) default NULL,
`text_spcl_instn` varchar(255) default NULL,
`dt_lst_updt` datetime default NULL,
`id_user_lst_updt` varchar(8) default NULL
) TYPE=MyISAM

I did read your response/answer to my problem and being a newbie, I found it 
quite complicated for me to follow. Does it really require more than just 1 
or 2 simple select queries to pull out rows ( unique id_secr_rqst - 2 of 
them for each) which have the max(dt_aud_rec) and second max(dt_aud_rec)? I 
mean simple queries with joins. I cannot use sub queries.

I am assured that the table will have no more than a 1000 records and after 
my initial filtering I will have to deal with 100 records maximum. So 
performance is not a problem at all.

If SGreen's response is the only one then its ok - I will try to follow that 
- else I think it has room for simplication a bit.

Thanks,
Anoop

On 5/24/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> Anoop kumar V <[EMAIL PROTECTED]> wrote on 05/24/2005 03:02:11 PM:
> 
> > Thanks Peter - you gave me some ideas...
> > here is what I have so far (simplified for simplification..)
> > 
> > select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
> t2
> > where t1.id_secr_rqst=t2.id_secr_rqst
> > and t1.dt_aud_rec > t2.dt_aud_rec
> > group by t1.id_secr_rqst
> > 
> > but the problem is that it only returns the record related to the second 
> 
> > largest date for each id_secr_rqst.
> > 
> > any suggestions how to get both the second and the largest date records 
> in 
> > the same query?
> > 
> > Thanks,
> > Anoop
> > 
> > 
> > On 5/23/05, Peter Normann <[EMAIL PROTECTED]> wrote:
> > > 
> > > Anoop kumar V  wrote:
> > > 
> > > > well - actually it might not be the last 2 days - i just want 2 of
> > > > the latest records for every task regardless of what date it is in
> > > > the table.
> > > 
> > > Okay, now I think I understand what you need - and if I am correct, 
> this
> > > looks like one of the more exotic querys to me, but then again, I'm 
> not 
> > > like
> > > the SQL king around, but let me give it a shot:
> > > 
> > > SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
> MAX(dt_aud_rec)
> > > Latest, MAX(dt_aud_rec) NoSoLatest
> > > FROM isr2_aud_log t1, isr2_aud_log t2
> > > WHERE t1.id_secr_rqst = t2.id_secr_rqst
> > > AND t1.name_rec_type='Exception Resource'
> > > AND dt_aud_rec = Latest
> > > OR dt_aud_rec = NoSoLatest
> > > HAVING Latest > NoSoLatest
> > > GROUP BY t1.id_secr_rqst
> > > ORDER by t1.dt_aud_rec DESC;
> > > 
> > > I am not 100% sure about the syntax, but you might get the idea.
> > > 
> > > Peter Normann
> > > 
> > > 
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: 
> http://lists.mysql.com/[EMAIL PROTECTED]
> > > 
> > > 
> > 
> > 
> > -- 
> > Thanks and best regards,
> > Anoop
>  
> I would solve this query by first constructing a table that contains the 
> information I need to identify the two most recent records (tasks). SINCE 
> YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
> forced to make up nearly every part of my answer. And because you want it to 
> be cross-database portable, I won't be able to use the group-wize autonumber 
> trick. However this will use a technique twice like the group-wize-maximum 
> technique (described here: 
> http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html ) 
> 
>

Re: complicated query | no Sub query

[SGreen]

Anoop kumar V <[EMAIL PROTECTED]> wrote on 05/24/2005 03:02:11 PM:

> Thanks Peter - you gave me some ideas...
> here is what I have so far (simplified for simplification..)
> 
> select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
t2
> where t1.id_secr_rqst=t2.id_secr_rqst
> and t1.dt_aud_rec > t2.dt_aud_rec
> group by t1.id_secr_rqst
> 
> but the problem is that it only returns the record related to the second 

> largest date for each id_secr_rqst.
> 
> any suggestions how to get both the second and the largest date records 
in 
> the same query?
> 
> Thanks,
> Anoop
> 
> 
> On 5/23/05, Peter Normann <[EMAIL PROTECTED]> wrote:
> > 
> > Anoop kumar V  wrote:
> > 
> > > well - actually it might not be the last 2 days - i just want 2 of
> > > the latest records for every task regardless of what date it is in
> > > the table.
> > 
> > Okay, now I think I understand what you need - and if I am correct, 
this
> > looks like one of the more exotic querys to me, but then again, I'm 
not 
> > like
> > the SQL king around, but let me give it a shot:
> > 
> > SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
MAX(dt_aud_rec)
> > Latest, MAX(dt_aud_rec) NoSoLatest
> > FROM isr2_aud_log t1, isr2_aud_log t2
> > WHERE t1.id_secr_rqst = t2.id_secr_rqst
> > AND t1.name_rec_type='Exception Resource'
> > AND dt_aud_rec = Latest
> > OR dt_aud_rec = NoSoLatest
> > HAVING Latest > NoSoLatest
> > GROUP BY t1.id_secr_rqst
> > ORDER by t1.dt_aud_rec DESC;
> > 
> > I am not 100% sure about the syntax, but you might get the idea.
> > 
> > Peter Normann
> > 
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 
> -- 
> Thanks and best regards,
> Anoop

I would solve this query by first constructing a table that contains the 
information I need to identify the two most recent records (tasks). SINCE 
YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
forced to make up nearly every part of my answer. And because you want it 
to be cross-database portable, I won't be able to use the group-wize 
autonumber trick. However this will use a technique twice like the 
group-wize-maximum technique (described here: 
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html )

CREATE TEMPORARY TABLE tmpMaxDates (
task_id varchar(10) not null,
task_date date not null,
INDEX(task_id, task_date) 
);

INSERT tmpMax (task_id, task_date)
SELECT task_ID, max(task_date)
FROM tasktable
GROUP BY task_ID;

#now collect the max(PK) value for each task_id/task_date pair

CREATE TEMPORARY TABLE tmpRecordsToProcess (
task_id varchar(10) not null,
task_date date not null,
task_pk int not null
INDEX(task_pk) 
);

INSERT tmpRecordsToProcess rtp (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
GROUP BY tt.task_id, tt.task_date;

# now to get the second record back

DELETE FROM tmpMaxDates;

INSERT tmpMaxDates (task_id, task_date)
SELECT tt.task_id, max(tt.task_date)
FROM tasktable tt
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_pk is null
GROUP BY tt.task_id;


INSERT tmpRecordsToProcess (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_PK is null
GROUP BY tt.task_id, tt.task_date;

Now (assuming I am not too hosed-up today) you should be able to process 
against tmpRecordsToProcess (using the task_pk field) to limit your 
queries to just those PK values you have identified as being the two most 
recent for each task (assuming a higher PK value is more recent than a 
lower one for the same task/date pair). This would have been much easier 
to code if we had been able to use the group-wize auto-increment feature 
of MyISAM. You can repeat the last 3 statements as often as you wish in 
order to build a larger "most recent" list. I am sure that if I made any 
logical errors, fresher minds on the list will catch them as I am nearing 
the end of a rather long day and could have easily mis-typed something.

To summarize: tmpRecordsToProcess should contain a list of the primary key 
values of the two "most recent" records for each task.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: complicated query | no Sub query

[Anoop kumar V]

Thanks Peter - you gave me some ideas...
here is what I have so far (simplified for simplification..)

select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2
where t1.id_secr_rqst=t2.id_secr_rqst
and t1.dt_aud_rec > t2.dt_aud_rec
group by t1.id_secr_rqst

but the problem is that it only returns the record related to the second 
largest date for each id_secr_rqst.

any suggestions how to get both the second and the largest date records in 
the same query?

Thanks,
Anoop


On 5/23/05, Peter Normann <[EMAIL PROTECTED]> wrote:
> 
> Anoop kumar V  wrote:
> 
> > well - actually it might not be the last 2 days - i just want 2 of
> > the latest records for every task regardless of what date it is in
> > the table.
> 
> Okay, now I think I understand what you need - and if I am correct, this
> looks like one of the more exotic querys to me, but then again, I'm not 
> like
> the SQL king around, but let me give it a shot:
> 
> SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec)
> Latest, MAX(dt_aud_rec) NoSoLatest
> FROM isr2_aud_log t1, isr2_aud_log t2
> WHERE t1.id_secr_rqst = t2.id_secr_rqst
> AND t1.name_rec_type='Exception Resource'
> AND dt_aud_rec = Latest
> OR dt_aud_rec = NoSoLatest
> HAVING Latest > NoSoLatest
> GROUP BY t1.id_secr_rqst
> ORDER by t1.dt_aud_rec DESC;
> 
> I am not 100% sure about the syntax, but you might get the idea.
> 
> Peter Normann
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Thanks and best regards,
Anoop

RE: complicated query | no Sub query

[Peter Normann]

Anoop kumar V  wrote:

> well - actually it might not be the last 2 days - i just want 2 of
> the latest records for every task regardless of what date it is in
> the table.  

Okay, now I think I understand what you need - and if I am correct, this
looks like one of the more exotic querys to me, but then again, I'm not like
the SQL king around, but let me give it a shot:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec)
Latest, MAX(dt_aud_rec) NoSoLatest
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst = t2.id_secr_rqst
AND t1.name_rec_type='Exception Resource'
AND dt_aud_rec = Latest
OR dt_aud_rec = NoSoLatest
HAVING Latest > NoSoLatest
GROUP BY t1.id_secr_rqst
ORDER by t1.dt_aud_rec DESC;

I am not 100% sure about the syntax, but you might get the idea.

Peter Normann


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: complicated query | no Sub query

[Peter Normann]

Anoop kumar V  wrote:

> 1. It returns data about only one id_secr_rqst - I want it to return
> data about every id_secr_rqst in the table.

So, if I understand you correctly (sorry, having a bad day), you want all
records for the past two days?

Assuming this, you could use something like:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst = t2.id_secr_rqst
AND t1.name_rec_type='Exception Resource' 
AND dt_aud_rec > CURDATE() - 2;
ORDER  by t1.dt_aud_rec DESC;

> 2. Limit IMO is mysql specific (I hope I am wrong) is there something
> generic so I dont need to bother about which database I am running it
> against.

As far as I know it is MySql specific. SQL Server's equivalent is SELECT TOP
2 or something.

Peter Normann


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: complicated query | no Sub query

[Anoop kumar V]

Thanks Peter - but I see two issues:

1. It returns data about only one id_secr_rqst - I want it to return data 
about every id_secr_rqst in the table.
2. Limit IMO is mysql specific (I hope I am wrong) is there something 
generic so I dont need to bother about which database I am running it 
against.

Thanks,
Anoop

On 5/23/05, Peter Normann <[EMAIL PROTECTED]> wrote:
> 
> Hi Anoop
> 
> Try:
> 
> SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec
> FROM isr2_aud_log t1, isr2_aud_log t2
> WHERE t1.id_secr_rqst =
> t2.id_secr_rqst AND
> t1.name_rec_type='Exception Resource'
> ORDER by t1.dt_aud_rec DESC
> LIMIT 2;
> 
> Peter Normann
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Thanks and best regards,
Anoop

RE: complicated query | no Sub query

[Peter Normann]

Hi Anoop

Try:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst =
t2.id_secr_rqst AND
t1.name_rec_type='Exception Resource' 
ORDER  by t1.dt_aud_rec DESC
LIMIT 2;

Peter Normann


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

complicated query | no Sub query

[Anoop kumar V]

hi All,

I need to create a query using no subqueries as I use 4.0.23 which does not 
support subqueries. I cannot upgrade for some compellimg reasons (the 
product does not support anything later than 4.0.23nt as of now). I also 
cannot use any thing that is native to mysql - in the sense that the query 
should be as generic/simple as possible so that I can run it against both 
DB2 or Sybase.

Here is the problem..

I have this table: (modified to simplify)

++
| id_secr| name_rec_type | dt_aud_rec |
++
| TASKD1 | Risk Assessment | 2005-05-20 19:07:54 |
| TASKD1 | Assigned | 2005-05-20 19:07:53 |
| TASKD1 | Pending | 2005-05-20 12:10:50 |
| TASKD2 | Closed | 2005-05-20 19:06:27 |
| TASKD2 | Risk Assessment | 2005-05-20 19:06:04 |
| TASKD2 | Pending | 2005-05-20 19:05:54 |
| TASKD3 | Closed | 2005-05-20 16:40:14 |
| TASKD3 | Risk Assessment | 2005-05-20 10:07:54 |
| TASKD3 | Assigned | 2005-05-20 10:00:54 |
| TASKD4 | Closed | 2005-05-20 10:34:13 |
| TASKD4 | Risk Assessment | 2005-05-20 09:07:54 |
| TASKD4 | Assigned | 2005-05-20 09:00:54 |
| TASKD4 | Assigned | 2005-05-20 09:00:04 |
| TASKD5 | Closed | 2005-05-20 15:33:13 |
| TASKD5 | SERB Assessment | 2005-05-20 15:07:54 |
| TASKD5 | Assigned | 2005-05-20 14:07:54 |
| TASKD5 | Risk Assessment | 2005-05-20 13:07:54 |
| TASKD5 | Risk Assessment | 2005-05-20 12:07:54 |
| TASKD6 | Closed | 2005-05-20 14:18:28 |
| TASKD6 | Risk Assessment | 2005-05-20 13:07:54 |
| TASKD6 | Assigned | 2005-05-20 12:07:54 |
| TASKD6 | Pending | 2005-05-20 11:07:54 |
| TASKD6 | Pending | 2005-05-20 10:07:54 |
| TASKD6 | Pending | 2005-05-20 09:07:54 |
| TASKD6 | Pending | 2005-05-20 08:07:54 |
| TASKD6 | Pending | 2005-05-20 07:07:54 |
++

what I need is to pull out data based on the latest two dates. I will be 
checking the status (name_rec_type) and if my status matches any one of the 
latest 2 name_rec_type I will do some processing. The only thing compounding 
this is that I cannot use any sub queries - I can do as many joins as 
necessary. 
Also the query need not be performance intensive as I dont think we will 
have more than 2000 rows at any time. Moreover I will have to ignore all 
rows (or wholes TASKD*'s) where the name_rec_type is closed anywhere.

here is what I have come up so far - but it gives only the latest data: (and 
not the last 2 latest)

select t1.id_secr_rqst, t2.name_rec_type, max(t1.dt_aud_rec) from 
isr2_aud_log t1, isr2_aud_log t2 where t1.id_secr_rqst = t2.id_secr_rqst and 
t1.name_rec_type='Exception Resource' group by t1.id_secr_rqst

I need help.
Thanks in advance.

Anoop

Re: Complicated Query

[Ian Sales (DBA)]

Ron Watson wrote:
This works, but only if a title and a role exists for the company member.
Basically, I want to display the season name from tbl Season, then the show
title from tbl Shows, then the roles from cast and titles from
production_team for the company member. 

 

- use LEFT JOINs.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Complicated Query

[Ron Watson]

Hello.

 

I'm looking for some help on a complicated query. I have data in 5 different
tables I want displayed on this page. The query I have now is 

 

$query = "SELECT Shows.Season_RID AS SEASON_NUM, 

 Shows.Show_Name AS NAME, 

 Shows.Show_RID AS SHOWX, 

 Season.Season_Name AS SEASON, 

 cast.Role AS CAST, 

 production_team.Title AS TITLE 

  FROM osc1_company, 

   production_team, 

   Season, 

   Shows, 

   cast 

  WHERE osc1_company.First_Name='$fname' AND


 osc1_company.Last_Name='$lname' AND 

 osc1_company.RID = cast.RID AND 

 osc1_company.RID = production_team.RID AND 

 production_team.Show_RID=Shows.Show_RID AND


 Shows.Season_RID = Season.Season_RID AND  

 Shows.Show_RID = cast.Show_RID 

  ORDER BY Season.Season_RID 

";

 

This works, but only if a title and a role exists for the company member.
Basically, I want to display the season name from tbl Season, then the show
title from tbl Shows, then the roles from cast and titles from
production_team for the company member. 

 

The table Season has Season_Name and Season_RID. The table Shows has
Season_RID, Show_RID, and Show_Name. osc1_company has First_Name, Last_Name,
and RID. Cast has RID, Role, Show_RID. Production_team has RID, title,
Show_RID.

 

Any help folks could give would be hugely appreciated. Been working on this
for quite a while now.

 

Thanks

 

Ron

 

Re: Complicated query problem

[beacker]

>The query as written works just fine although I'm certain there's got to be
>a more efficient way of doing the same thing.  I'm relatively new to MySQL
>so I took the brute force approach.
>
>My problem is that I want to produce totals of each of the columns and can't
>figure out how to do it.  Any suggestions on how I can do this?

 Might I suggest a mapping table relating speed to the labels desired
and the speed ratings (int -> strings).  That way it's extensible and
can easily be joined to the original query?
   Brad Eacker ([EMAIL PROTECTED])



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Complicated query problem

[Jack Coxen]

I've got the following query that produces a large table for me.

SELECT
router.name AS Router,
SUM(IF(speed<='64000',1,0)) AS 64K,
SUM(IF(speed='128000',1,0)) AS 128K,
SUM(IF(speed='192000',1,0)) AS 192K,
SUM(IF(speed='256000',1,0)) AS 256K,
SUM(IF(speed='384000',1,0)) AS 384K,
SUM(IF(speed='512000',1,0)) AS 512K,
SUM(IF(speed='768000',1,0)) AS 768K,
SUM(IF(speed='1024000',1,0)) AS 1M,
SUM(IF(speed='1152000',1,0)) AS 1152K,
SUM(IF(speed='128',1,0)) AS 1280K,
SUM(IF(speed='1536000'
OR speed='1544000',1,0)) AS 'V/T1',
SUM(IF(speed='300',1,0)) AS 3M,
SUM(IF(speed='600',1,0)) AS 6M,
SUM(IF(speed='900',1,0)) AS 9M,
SUM(IF(speed='1000',1,0)) AS 10M,
SUM(IF(speed='1200',1,0)) AS 12M,
SUM(IF(speed='2400',1,0)) AS 24M,
SUM(IF(speed BETWEEN '4000' AND '4600',1,0)) AS 'DS3/T3',
SUM(IF(speed='1',1,0)) AS 100M,
SUM(IF(speed='15500',1,0)) AS OC3,
SUM(IF(speed BETWEEN '59900' AND '65000',1,0)) AS OC12,
SUM(IF(speed='10',1,0)) AS Gigabit,
SUM(IF(speed='115000',1,0)) AS Dialup,
SUM(IF(speed>'64000'
AND speed!='128000'
AND speed!='192000'
AND speed!='256000'
AND speed!='384000'
AND speed!='512000'
AND speed!='768000'
AND speed!='1024000'
AND speed!='1152000'
AND speed!='128'
AND speed!='1536000'
AND speed!='1544000'
AND speed!='300'
AND speed!='600'
AND speed!='900'
AND speed!='1000'
AND speed!='1200'
AND speed!='2400'
AND speed NOT BETWEEN '4000' AND '4600'
AND speed!='1'
AND speed!='15500'
AND speed NOT BETWEEN '59900' AND '65000'
AND speed!='10'
AND speed!='115000',1,0)) AS Other,
COUNT(*) AS Total
FROM router INNER JOIN interface USING (rid)
GROUP BY router.rid
ORDER BY router.name;

The query as written works just fine although I'm certain there's got to be
a more efficient way of doing the same thing.  I'm relatively new to MySQL
so I took the brute force approach.

My problem is that I want to produce totals of each of the columns and can't
figure out how to do it.  Any suggestions on how I can do this?

Thanks,

Jack

Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705

Re: Complicated query. query help

[Roger Baklund]

* TimeWalker 
> I've been trying to work out a complicated query to select and 
> sum  multiple columns in one select using a condition and multi 
> column group by
> 
> 
> What I expected to get was ONE row  with the columns summed .
> 
> this query returns 5 rows
> 
> SELECT sum(`AllTheWeb`),sum(`AltaVista`),sum(`AOL`),sum(`Ask`),sum(`Ask 
> Jeeves`),sum(`Cest 
> trouve`),sum(`DirectHit`),sum(`DMOZ`),sum(`Dogpile`),sum(`Euroseek
> `),sum(`Excite`),sum(`Fireball`),sum(`FrancitT`),sum(`Go2Net`),sum
> (`Google`),sum(`Hotbot`),sum(`Kanoodle`),sum(`LBB`),sum(`Libertysu
> rf`),sum(`Lokace`),sum(`Lycos`),sum(`Mamma`),sum(`MegaSpider`),sum
> (`MetaCrawler`),sum(`MetaGer`),sum(`MSN`),sum(`NBCI`),sum(`Netscap
> e`),sum(`Nomade`),sum(`NorthernLight`),sum(`Overture`),sum(`Spray`
> ),sum(`Terra`),sum(`Vindex.nl`),sum(`Voila`),sum(`Web.de`),sum(`We
> bCrawler`),sum(`WebSearch`),sum(`Yahoo`) 
> FROM engine where sitenum = "2365602" GROUP BY 
> `AllTheWeb`,`AltaVista`,`AOL`,`Ask`,`Ask Jeeves`,`Cest 
> trouve`,`DirectHit`,`DMOZ`,`Dogpile`,`Euroseek`,`Excite`,`Fireball
> `,`FrancitT`,`Go2Net`,`Google`,`Hotbot`,`Kanoodle`,`LBB`,`Libertys
> urf`,`Lokace`,`Lycos`,`Mamma`,`MegaSpider`,`MetaCrawler`,`MetaGer`
> ,`MSN`,`NBCI`,`Netscape`,`Nomade`,`NorthernLight`,`Overture`,`Spra
> y`,`Terra`,`Vindex.nl`,`Voila`,`Web.de`,`WebCrawler`,`WebSearch`,`Yahoo`

Have you tried this:

  SELECT sum(... WHERE sitenum = "2365602" GROUP BY sitenum

...should return one row...

-- 
Roger
sql

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Complicated query. query help

[TimeWalker]

I've been trying to work out a complicated query to select and 
sum  multiple columns in one select using a condition and multi column group by


What I expected to get was ONE row  with the columns summed .

this query returns 5 rows

SELECT sum(`AllTheWeb`),sum(`AltaVista`),sum(`AOL`),sum(`Ask`),sum(`Ask 
Jeeves`),sum(`Cest 
trouve`),sum(`DirectHit`),sum(`DMOZ`),sum(`Dogpile`),sum(`Euroseek`),sum(`Excite`),sum(`Fireball`),sum(`FrancitT`),sum(`Go2Net`),sum(`Google`),sum(`Hotbot`),sum(`Kanoodle`),sum(`LBB`),sum(`Libertysurf`),sum(`Lokace`),sum(`Lycos`),sum(`Mamma`),sum(`MegaSpider`),sum(`MetaCrawler`),sum(`MetaGer`),sum(`MSN`),sum(`NBCI`),sum(`Netscape`),sum(`Nomade`),sum(`NorthernLight`),sum(`Overture`),sum(`Spray`),sum(`Terra`),sum(`Vindex.nl`),sum(`Voila`),sum(`Web.de`),sum(`WebCrawler`),sum(`WebSearch`),sum(`Yahoo`) 
FROM engine where sitenum = "2365602" GROUP BY 
`AllTheWeb`,`AltaVista`,`AOL`,`Ask`,`Ask Jeeves`,`Cest 
trouve`,`DirectHit`,`DMOZ`,`Dogpile`,`Euroseek`,`Excite`,`Fireball`,`FrancitT`,`Go2Net`,`Google`,`Hotbot`,`Kanoodle`,`LBB`,`Libertysurf`,`Lokace`,`Lycos`,`Mamma`,`MegaSpider`,`MetaCrawler`,`MetaGer`,`MSN`,`NBCI`,`Netscape`,`Nomade`,`NorthernLight`,`Overture`,`Spray`,`Terra`,`Vindex.nl`,`Voila`,`Web.de`,`WebCrawler`,`WebSearch`,`Yahoo`


the simplest query  select * from engine where sitenum = "2365602"  returns 
15 rows


Did I find a bug (either on the query side)  on the error checking side, 
where by it's allowing a disallow able query or is that query just SOO 
badly formatted that it'll never work but is allowable?



I really need some help :)   to execute "THAT" query  where by getting one 
row with those columns summed


Thanks Rick



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

complicated query

[Terry]

hi,

i am trying to move one of the databases we have in access
to mysql and i have come accross a query that mysql doesnt like:

SELECT Managers.Manager, Agents.Agent, Modules.Module, Messages.MLevel, 
Messages.Title, MessageInstance.Name, MessageInstance.Info, PolicyRuns.JobNo, 
Policies.Policy FROM Messages INNER JOIN (Managers INNER JOIN (((PolicyRuns INNER JOIN 
((Modules INNER JOIN PolicyModule ON Modules.ID = PolicyModule.ModuleID) INNER JOIN 
Policies ON PolicyModule.PolicyID = Policies.ID) ON (PolicyRuns.PolicyID = 
Policies.ID) AND (PolicyRuns.ModuleID = Modules.ID)) INNER JOIN MessageInstance ON 
PolicyRuns.ID = MessageInstance.PolicyRunID) INNER JOIN Agents ON PolicyRuns.AgentID = 
Agents.ID) ON (Managers.ID = Agents.ManagerID) AND (Managers.ID = Policies.ManagerID)) 
ON Messages.ID = MessageInstance.MessageID
WHERE (((Policies.Policy)="CM-First Audit")); 

what might be possibly wrong with the query?
how can i make it work in mysql ?

thanx in advance,
terry


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Complicated Query?

[Roger Baklund]

* Jerry Rehak
> I have a table with the columns names and id.  I want to be able
> to find all
> names with an id of '03' that do not have other records with id values of
> '10','20' or '37'.
>
> Is this even possible to do?
>
> name   id
> a  03
> a  11
> a  12
> a  13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
> b  03
> b  10 I don't want 'b' because it has a 10
> c  04
> c  11
> c  20 I don't want 'c' because it doesn't have a 03
> d  03 I want 'd' because it has a 03 and no other records

This can be done with a simple LEFT JOIN:

SELECT t1.*
  FROM table AS t1
  LEFT JOIN table AS t2 ON
t2.name=t1.name AND
t2.id IN ('10','20','37')
  WHERE t1.id='03' AND t2.id IS NULL;

We select the rows we want from t1, left join with the rows we don't want
(t2), and put as a condition in the where clause that we only want rows
where t2 was not found.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Complicated Query?

[Rick Emery]

Needs to be done programmatically

-Original Message-
From: Jerry Rehak [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 10:53 AM
To: [EMAIL PROTECTED]
Subject: Complicated Query?


I have a table with the columns names and id.  I want to be able to find all
names with an id of '03' that do not have other records with id values of
'10','20' or '37'.

Is this even possible to do?

name   id
a03
a11
a12
a13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
b03
b10 I don't want 'b' because it has a 10
c04
c11
c20 I don't want 'c' because it doesn't have a 03
d03 I want 'd' because it has a 03 and no other records

Thanks for your help


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Complicated Query?

[Jerry Rehak]

I have a table with the columns names and id.  I want to be able to find all
names with an id of '03' that do not have other records with id values of
'10','20' or '37'.

Is this even possible to do?

name   id
a03
a11
a12
a13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
b03
b10 I don't want 'b' because it has a 10
c04
c11
c20 I don't want 'c' because it doesn't have a 03
d03 I want 'd' because it has a 03 and no other records

Thanks for your help


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Please help!!!!!!Re: order by...group by...complicated query

[hassan el forkani]

>hi,
>
>i am trying to build a message board in php with mysql as back end;
>
>what i want to do is to query the database in a certain way that the 
>result is returned in the correct order for php to display the discussion 
>thread properly
>
>here is my table structure:
>
>mysql> show fields from posts
> -> ;
>+-+-+--+-+-++
>| Field   | Type| Null | Key | Default | Extra  |
>+-+-+--+-+-++
>| msgid   | bigint(20) unsigned |  | PRI | NULL| auto_increment |
>| dateadded   | timestamp(14)   | YES  | | NULL||
>| subject | mediumblob  |  | | ||
>| body| longblob| YES  | | NULL||
>| replytopost | bigint(20) unsigned | YES  | | 0   ||
>| aposition   | tinyint(3) unsigned | YES  | | 0   ||
>| thrid   | bigint(20) unsigned |  | MUL | 0   ||
>| usrid   | bigint(20) unsigned |  | MUL | 0   ||
>| username| varchar(50) |  | | ||
>+-+-+--+-+-++
>9 rows in set (0.00 sec)
>
>the query should look like : select * from posts where thrid = 'the id of 
>the thread' order by.(this is the part i couldn't figure out);
>
>the position of a single message into the tree is determined by the 
>following criteria:
>dateadded : the date in which it was added
>replytopost: the post to which it belongs, if none then 0 is assigned
>aposition: the absolute position of the message (horizontal)
>
>a combination of these columns in an order by clause should (in theory) 
>build the tree correctly
>please advise me if i am doing something wrong here, i have tried many 
>combinations without success
>
>regards;
>
>hassan
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

order by...group by...complicated query

[hassan el forkani]

hi,

i am trying to build a message board in php with mysql as back end;

what i want to do is to query the database in a certain way that the result 
is returned in the correct order for php to display the discussion thread 
properly

here is my table structure:

mysql> show fields from posts
 -> ;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| msgid   | bigint(20) unsigned |  | PRI | NULL| auto_increment |
| dateadded   | timestamp(14)   | YES  | | NULL||
| subject | mediumblob  |  | | ||
| body| longblob| YES  | | NULL||
| replytopost | bigint(20) unsigned | YES  | | 0   ||
| aposition   | tinyint(3) unsigned | YES  | | 0   ||
| thrid   | bigint(20) unsigned |  | MUL | 0   ||
| usrid   | bigint(20) unsigned |  | MUL | 0   ||
| username| varchar(50) |  | | ||
+-+-+--+-+-++
9 rows in set (0.00 sec)

the query should look like : select * from posts where thrid = 'the id of 
the thread' order by.(this is the part i couldn't figure out);

the position of a single message into the tree is determined by the 
following criteria:
dateadded : the date in which it was added
replytopost: the post to which it belongs, if none then 0 is assigned
aposition: the absolute position of the message (horizontal)

a combination of these columns in an order by clause should (in theory) 
build the tree correctly
please advise me if i am doing something wrong here, i have tried many 
combinations without success

regards;

hassan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

complicated query

[Daren Cotter]

I got my query to work...

However, this query is going to be used a LOT, so I want to make sure it's
completely optimized. I have keys on all fields that I should...here's the
query I'm using:

select m.member_id, t.num_questions, r.score FROM members as m, trivia as t,
results as r WHERE t.trivia_id = 16 AND r.trivia_id = t.trivia_id AND
m.username = 'daren_cotter' AND m.member_id = r.member_id;

Using explain:

explain select m.member_id, t.num_questions, r.score FROM members as m,
trivia as t, results as r WHERE t.trivia_id = 16 AND r.trivia_id =
t.trivia_id AND m.username = 'daren_cotter' AND m.member_id = r.member_id;
+---++--+---
--+-+---+--+---+
| table | type   | possible_keys| key
| key_len | ref   | rows | Extra |
+---++--+---
--+-+---+--+---+
| r | system | PRIMARY,results_trivia_id_member_id  | NULL
|NULL | NULL  |1 |   |
| m | const  | PRIMARY,members_username_unique,members_username |
PRIMARY |   3 | const |1 |   |
| t | const  | PRIMARY  |
PRIMARY |   2 | const |1 |   |
+---++--+---
--+-+---+--+---+
3 rows in set (0.00 sec)

Can someone please tell me if this is optimized?

TIA,



Daren Cotter


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

complicated query

[Daren Cotter]

I have 3 tables (applicable fields shown below):

members:
member_id, username

trivia:
trivia_id, num_questions

results:
trivia_id, member_id, score

I need a query that will return: the score and number of questions for a
given trivia, and the information I know is the member's username. Here's
the query I tried:

select m.member_id, t.num_questions, r.score FROM members AS m, trivia AS t,
results AS r WHERE m.username = 'username' AND r.trivia_id = 16 AND
r.member_id = m.member_id;

This returns invalid data, however. Can someone please help me out with this
query?

TIA,


Daren Cotter


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Complicated Query...

[$B$U(B]

Hi there,
I'm having a problem on my complicated query.
My question is similar to the question in a couple of emails earlier in
this mailing list which subject's is "complicated query".

I got 3 tables below.

 table
++--+
| id | name |
++--+
|  1 | fumi |
|  2 | ali  |
|  3 | ton  |
++--+

 table
+++---+
| id | maker  | price |
+++---+
|  1 | fum111 |   102 |
|  3 | ton222 |   150 |
+++---+

 table
++--+
| id | location |
++--+
|  3 | LA   |
++--+

when I commit my query "select test1.*,test2.*,test3.* from test1 left join test2 on 
test1.id = test2.id left join te
st3 on test1.id = test3.id;",I get this table below.
++--+--++---+--+--+
| id | name | id   | maker  | price | id   | location |
++--+--++---+--+--+
|  1 | fumi |1 | fum111 |   102 | NULL | NULL |
|  2 | ali  | NULL | NULL   |  NULL | NULL | NULL |
|  3 | ton  |3 | ton222 |   150 |3 | LA   |
++--+--++---+--+--+

Now I wanna sort this table by smaller price. (I want NULL to be at the last)
so I made a new query " select test1.*,test2.*,test3.* from test1 left join test2 on 
test1.id = test2.id left join te
st3 on test1.id = test3.id  order by (price is null);" according to a couple
of email earlier in this mailing list.

I get the table below.
++--+--++---+--+--+
| id | name | id   | maker  | price | id   | location |
++--+--++---+--+--+
|  3 | ton  |3 | ton222 |   150 |3 | LA   |
|  1 | fumi |1 | fum111 |   102 | NULL | NULL |
|  2 | ali  | NULL | NULL   |  NULL | NULL | NULL |
++--+--++---+--+--+

In a price column, NULL is at the last, so it's okay, BUT !!
150 is upper than 102 !! I want smaller price to show up upper and
finally NULL.

I've tried many queries , but couldn't find out...
anybody can give me a hint or answer ?

Thank you for reading till here.

Fumitada.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: complicated query

[Milo Stefani]

try
ORDER BY (Category = 'Other'), Category, Subcategory
It should work, (I hope ^_-;;;)

- Original Message -
From: "Jeff Shipman - SysProg" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 20, 2001 8:38 PM
Subject: complicated query


> I would like to do something similar to an ORDER BY
> in one of my select statements, but I'mt not sure
> how to do something as complicated as this:
>
> I have two columns, category and subcategory, that
> I am retrieving. I would like category and subcategory
> to be sorted alphabetically. This is easy with an
> 'ORDER BY 1 2', but I would like categories that
> are named 'other' to be put off until the end. So,
> I'd get something like this:
>
> abcd
> ghikj
> z
> other
>
> Is there a way to do this type of query? Thanks in
> advance.
>
> Jeff Shipman   E-Mail: [EMAIL PROTECTED]
> Systems Programmer Phone: (505) 835-5748
> NMIMT Computer Center  http://www.nmt.edu/~jeff
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: complicated query

[Rick Pasotto]

On Fri, Apr 20, 2001 at 03:09:32PM -0600, Jeff Shipman - SysProg wrote:
> }
> } You don't need to create another column, just use:
> }
> } ORDER BY category = 'other', category
> }
> 
> Are you sure this works? My query is:
> 
> select category,subcategory from categories ORDER BY
> category = 'other', category;
> 
> And I get:
> 
> ERROR 1064: You have an error in your SQL syntax
> near '= 'other', category' at line 1

Yes. And I of course tested before I posted. I also tried 

ORDER BY category in ('cat1','cat2'), category

which worked.

I'm running 3.23.36 on debian linux.

Well, actually, rather than create a new table, I used an existing one.
The actual statement was

select last_name from mizpah where first_name like 'fr%' order by
last_name = 'crosby', last_name;

-- 
"Moderation in temper is always a virtue; but moderation in
 principle is always a vice."
-- Thomas Paine, _The Rights of Man_ (1791)
   Rick Pasotto email: [EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: complicated query

[Jeff Shipman - SysProg]

This does not work for me. It returns syntax
errors.

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff

On Fri, 20 Apr 2001, Braxton Robbason wrote:

} you want a function that prepends the letter a to the category/subcategory
} names that are not other.  then you order by that function, but do not
} display it.
}
} select category,subcategory from foo2
} order by if(category=
} 'other','zz',concat('a',category));
} i.e. everything except other begins with an a as far as the order by is
} concerned, while other is zz for sorting purposes.
}
}
} -Original Message-
} From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
} Sent: Friday, April 20, 2001 2:39 PM
} To: [EMAIL PROTECTED]
} Subject: complicated query
}
}
} I would like to do something similar to an ORDER BY
} in one of my select statements, but I'mt not sure
} how to do something as complicated as this:
}
} I have two columns, category and subcategory, that
} I am retrieving. I would like category and subcategory
} to be sorted alphabetically. This is easy with an
} 'ORDER BY 1 2', but I would like categories that
} are named 'other' to be put off until the end. So,
} I'd get something like this:
}
} abcd
} ghikj
} z
} other
}
} Is there a way to do this type of query? Thanks in
} advance.
}
} Jeff Shipman   E-Mail: [EMAIL PROTECTED]
} Systems Programmer Phone: (505) 835-5748
} NMIMT Computer Center  http://www.nmt.edu/~jeff
}
}
}
} -
} Before posting, please check:
}http://www.mysql.com/manual.php   (the manual)
}http://lists.mysql.com/   (the list archive)
}
} To request this thread, e-mail <[EMAIL PROTECTED]>
} To unsubscribe, e-mail
} <[EMAIL PROTECTED]>
} Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
}
}
}
} -
} Before posting, please check:
}http://www.mysql.com/manual.php   (the manual)
}http://lists.mysql.com/   (the list archive)
}
} To request this thread, e-mail <[EMAIL PROTECTED]>
} To unsubscribe, e-mail <[EMAIL PROTECTED]>
} Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
}


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: complicated query

[Jeff Shipman - SysProg]

}
} You don't need to create another column, just use:
}
} ORDER BY category = 'other', category
}

Are you sure this works? My query is:

select category,subcategory from categories ORDER BY
category = 'other', category;

And I get:

ERROR 1064: You have an error in your SQL syntax
near '= 'other', category' at line 1

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: complicated query

[Rick Pasotto]

On Fri, Apr 20, 2001 at 03:39:59PM -0400, Steve Werby wrote:
> "Jeff Shipman - SysProg" <[EMAIL PROTECTED]> wrote:
> > I would like to do something similar to an ORDER BY
> > in one of my select statements, but I'mt not sure
> > how to do something as complicated as this:
> >
> > I have two columns, category and subcategory, that
> > I am retrieving. I would like category and subcategory
> > to be sorted alphabetically. This is easy with an
> > 'ORDER BY 1 2', but I would like categories that
> > are named 'other' to be put off until the end. So,
> > I'd get something like this:
> >
> > abcd
> > ghikj
> > z
> > other
> >
> > Is there a way to do this type of query? Thanks in
> > advance.
> 
> If you make the category field an ENUM type then it will automatically be
> sorted in the same order as the order of the ENUM values.  Another option
> would be to create an additional column (or 2 columns if the same problem
> occurs in "subcategory") and assign records with a category of "other" a
> value of 1 and assign all other records a default value of 0.  Let's call
> the new field "order_1" Then you could construct an ORDER BY clause like:
> 
> ORDER BY order_1, category...

You don't need to create another column, just use:

ORDER BY category = 'other', category

-- 
"Freedom is just chaos with better lighting."
-- Alan Dean Foster
   Rick Pasotto email: [EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: complicated query

[Steve Werby]

"Jeff Shipman - SysProg" <[EMAIL PROTECTED]> wrote:
> I would like to do something similar to an ORDER BY
> in one of my select statements, but I'mt not sure
> how to do something as complicated as this:
>
> I have two columns, category and subcategory, that
> I am retrieving. I would like category and subcategory
> to be sorted alphabetically. This is easy with an
> 'ORDER BY 1 2', but I would like categories that
> are named 'other' to be put off until the end. So,
> I'd get something like this:
>
> abcd
> ghikj
> z
> other
>
> Is there a way to do this type of query? Thanks in
> advance.

If you make the category field an ENUM type then it will automatically be
sorted in the same order as the order of the ENUM values.  Another option
would be to create an additional column (or 2 columns if the same problem
occurs in "subcategory") and assign records with a category of "other" a
value of 1 and assign all other records a default value of 0.  Let's call
the new field "order_1" Then you could construct an ORDER BY clause like:

ORDER BY order_1, category...

--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: complicated query

[Braxton Robbason]

you want a function that prepends the letter a to the category/subcategory
names that are not other.  then you order by that function, but do not
display it.

select category,subcategory from foo2
order by if(category=
'other','zz',concat('a',category));
i.e. everything except other begins with an a as far as the order by is
concerned, while other is zz for sorting purposes.


-Original Message-
From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 20, 2001 2:39 PM
To: [EMAIL PROTECTED]
Subject: complicated query


I would like to do something similar to an ORDER BY
in one of my select statements, but I'mt not sure
how to do something as complicated as this:

I have two columns, category and subcategory, that
I am retrieving. I would like category and subcategory
to be sorted alphabetically. This is easy with an
'ORDER BY 1 2', but I would like categories that
are named 'other' to be put off until the end. So,
I'd get something like this:

abcd
ghikj
z
other

Is there a way to do this type of query? Thanks in
advance.

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

complicated query

[Jeff Shipman - SysProg]

I would like to do something similar to an ORDER BY
in one of my select statements, but I'mt not sure
how to do something as complicated as this:

I have two columns, category and subcategory, that
I am retrieving. I would like category and subcategory
to be sorted alphabetically. This is easy with an
'ORDER BY 1 2', but I would like categories that
are named 'other' to be put off until the end. So,
I'd get something like this:

abcd
ghikj
z
other

Is there a way to do this type of query? Thanks in
advance.

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php